September 21, 2026 at 8:47 pm
I have about 100 Tables that are all suffixed with _Modify. These tables record changes made to these 100 tables. Each of these tables may or may not contain a column called 'User' to record the userid of who made the change.
I can get the names of all the tables with the following query:
WITH CTE1 AS
(
select
TABLE_NAME [TableName],
COLUMN_Name [ColumnName]
FROM INFORMATION_SCHEMA_COLUMNS
WHERE TABLE_NAME like '%_Modify'
and ColumnName = 'User'
)
Select TableName from CTE1
What I want to do next, is to query each table in that result set for some criteria using that User column.
I don't really want to use a cursor to loop through each of the tables and run a separate query against each table for a specific user,
is there a better way of running the query for each of those 100 tables?
September 21, 2026 at 9:30 pm
What I want to do next, is to query each table in that result set for some criteria using that User column.
I don't really want to use a cursor to loop through each of the tables and run a separate query against each table for a specific user,
I think you mean something like this:
Wildcard Tables — BigQuery supports querying multiple tables using wildcard syntax, which is useful for selecting from tables with similar naming conventions. For example, SELECT * FROM dataset.table_* retrieves data from all tables that match the pattern.
But unless I've completely missed it for the last several years, SQL Server doesn't do that. You'd likely have to create a temporary table to hold the result, and then create a query that uses dynamic SQL so you can set the source table in code (like loop through a cursor of table names) and then glue the SQL together and finally execute it to dump the data into a table (could be temporary) and finally return all the results.
I'd love to be proven wrong (not like it doesn't happen all the time on here), but I think that's the only way to do it.
September 22, 2026 at 2:53 pm
Dynamically create a view that references all the applicable tables at the moment your code started running.
For example, roughly like this. [Edits in italics] You didn't give any real details on what you want the code to do
therefore, unfortunately, I couldn't provide any more specific code below:
DECLARE @sql nvarchar(max);
;WITH CTE1 AS
(
select o.name AS TableName,
c.name AS ColumnName
FROM sys.columns c
INNER JOIN sys.objects o on o.object_id = c.object_id
WHERE o.name like '%[_]Modify'
and o.type = 'u'
and c.name = 'User'
)
Select @sql = String_Agg(Replace(N'Select ... from dbo.[$table$] Where ... ', '$table$', TableName), N' UNION ALL ')
from CTE1
Set @sql = N'Drop View If Exists Modify_View;
GO
CREATE VIEW dbo.Modify_Viwe
AS
' + @sql
Select @sql AS [--sql]
EXEC(@sql);
GO
SELECT *
FROM dbo.Modify_View
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 23, 2026 at 6:24 pm
Thank you both for your responses, very much appreciated!
I've attempted ScottPletcher's solution and that may just suit my needs, so thanks for that suggestion.
September 24, 2026 at 5:29 pm
There isn't a great way to do this. The view thing works, but it's still creating a big query. Is this a spur of the moment thing or a regular thing. What I might get at is perhaps you want to have this run regularly (every night) and put some information in a consolidate table for querying. Or if this is an audit thing, perhaps you want to design the _modify structures to put all that data in one place. I assume a trigger / proc /app puts data here. Could it put data in a single table and you change all the _modify tables to views.
September 24, 2026 at 9:33 pm
Steve,
isn't the only other option doing something like Scott started (return all the tables ending in "_Modify" and containing a column with the correct name), and then inserting the results into a table or unioning them together to return a result?
I guess it depends on what the OP wants to do with the returned records... so, @redsoxray, what do you want to do with the records once you have them?
September 24, 2026 at 10:33 pm
You have to query every table. However, 1 large view means S locks on lots/all tables as you combine them (potentially). A cursor means fewer locks at once, but serial work and the results need combination. There's also the work to create/update the view.
Moving the data could be a time-shift option, getting the data when it's updated, which is what I'm suggesting if the querying is a regular occurrence. If it's ad hoc, a cursor is simple and easy.
September 25, 2026 at 1:47 am
What do you want to do with the records returned from your query? If you want to update them, then you can't use a UNION query, because they're not updateable. In that case, you'd be stuck with using a cursor. (I know that cursors are somewhat a tool of last resort, but you would only be using it to execute a query per table, not one per record, so it wouldn't be terrible. Then you could write the records somewhere (like a table in tempdb or a permanent table). It's hard to tell which option would work better because you haven't provided any information about what the tables in question. Are they being continuously updated? How often did you need this result returned? What are you going to do with it? Not trying to be difficult, but it's hard to eliminate options when you haven't provided enough information to choose one option over another.
September 25, 2026 at 2:13 am
So maybe do everything inside a stored procedure, write the results to a temporary table, and at the end, query that table? (Only really necessary if the data changes all the time, though.)
One upside is that you'd have only one insert query and just use dynamic SQL to change the source table each time. You could just do like Scott did and use REPLACE on your query string to update the source table, execute the query, and go on to the next table.
September 25, 2026 at 5:45 pm
All depends on what the workload is. I might use the view approach, or I might do something differently. Hence why I asked the OP questions.
September 25, 2026 at 6:15 pm
Ray,
Could you post the solution you came up with? Just wondering how you did it.
September 25, 2026 at 10:54 pm
Thanks to all for your comments, suggestions, recommendations etc.
Apologies for not includeing more specific information about what I was trying to accomplish.
In a nutshell, I wanted to query all _Modify tables for specific users to see how many entries in each of the _Modify tables that each user had.
So, for example:
check the number of rows in TableA_Modify For users x, y and z
check the number of rows in TableB_Modify For users x, y and z
check the number of rows in TableC_Modify For users x, y and z
Tabulate the counts of each user in each table and display the results.
I was trying to figure out a way to automate this, and ultimately I think I was overcomplicating the solution.
I just wrote a query to get the list of all of the _Modify tables and populate those into a temp table, then coded a prefix to generate a select * from each of those tables for those users. After that I copy the generated SQL Selects into a query window and run it. Takes about a minute to run, but it only has to be done once a week.
In any case thanks again for everyone's help.
September 26, 2026 at 4:07 am
I think you could do it sort of like this... (well, the gory details are a bit off, but the core structure is close). Basically, you'd create your SQL statement to return the counts from the given table and insert them into the final (or temporary) table, and then you'd just parameterize that and replace the tablename inside your loop... kinda like this:
use itchy;
GO
DECLARE @TableName NVARCHAR(50);
DECLARE @SQLInsert NVARCHAR(500);
DECLARE @SQLTemplate NVARCHAR(500) = 'INSERT INTO MyTable(col1, col2) SELECT colA, colB FROM [table_name] WHERE colB > 10 AND colA < 100;'
DECLARE Tables_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT name AS TableName
FROM sys.all_objects ao
WHERE Type_desc = 'USER_TABLE'
AND name LIKE '%_Modify';
OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor
INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
/* stuff the tablename that we got from the cursor into the statement */
SET @SQLInsert = REPLACE(@SQLTemplate,'table_name',@TableName);
/* execute the statement */
EXECUTE sp_executesql @SQLInsert;
FETCH NEXT FROM Tables_cursor INTO @TableName
END
CLOSE Tables_cursor;
Then you could just modify the SQL in the @SQLTemplate example and then the cursor would handle looping over the table list and execute each statement.
September 28, 2026 at 10:03 pm
It seems like the table name might be useful to include in the output. Also, since sys.tables contains schema level names it could make sense to include schema_name in the output as well. Maybe something like this
-- 1. setup temp tables and vars
drop table if exists #target_table;
create table #target_table (
schema_name sysname not null,
table_name sysname not null,
user_val varchar(100) not null,
row_count int not null);
drop table if exists #users;
create table #users (
user_val varchar(100) primary key);
insert into #users (user_val)
values ('bob'), ('becky'), ('abdul');
declare
@col_name sysname = 'User',
@sql nvarchar(max);
-- 2. build dynamic sql string using schema + table
with eligible_tables as (
select
s.name as schema_name,
t.name as table_name
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
join sys.columns c on t.object_id = c.object_id
where t.is_ms_shipped = 0 -- table is not created by ms
and t.name like '%[_]Modify'
and c.name = @col_name
)
select @sql = string_agg(
cast(N'select ' +
quotename(schema_name, '''') + N', ' +
quotename(table_name, '''') + N', ' +
N't.' + quotename(@col_name) + N', ' +
N'count(*) ' +
N'from ' + quotename(schema_name) + N'.' + quotename(table_name) + N' t ' +
N'join #users u on t.' + quotename(@col_name) + N' = u.user_val ' +
N'group by t.' + quotename(@col_name) as nvarchar(max)),
N' union all '
)
from eligible_tables;
--print (@sql);
-- 3. execute and populate target
if @sql is not null
begin
insert into #target_table (schema_name, table_name, user_val, row_count)
exec sp_executesql @sql;
end
-- 4. view output
select schema_name, table_name, user_val, row_count
from #target_table
order by schema_name, table_name, user_val;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 28, 2026 at 10:20 pm
I like Scott's approach overall, though I dislike temp tables. Sure, they work, but if this fails or gets stopped/interrupted, I like to know where things are. I'd likely make this a permanent table, and add my spid or a batchID to the run, so that I could examine this at my leisure, or reload it if needed. This also lets multiple people perform searches, or me do multiple searches (parallel or sequential), without losing results.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply