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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply