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?