DBayliss - I looked at your other posts, and I think you've been landed with this task to maintain someone elses questionable code.

Brandie's Idea (2), while not ideal, can be done as follows.

First get the tables of type Agent - using the column names common to the Agent Tables as shown here (you can extend it to all the column names for greater accuracy)

SELECT DISTINCT T.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES AS T

JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_NAME = T.TABLE_NAME

WHERE C.COLUMN_NAME IN ( 'Start_Time' , 'End_Time', 'Record_Type', 'User_Action')

Then Dynamically create a SELECT statement from each of these, UNIONing them all together I've only included a few of the columns in the select - as follows.

Note: The fancy row_number bit is just to identify the last row, so it doesn't get a UNION ALL following.

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '';

;WITH AgentTables AS (

SELECT DISTINCT T.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES AS T

JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_NAME = T.TABLE_NAME

WHERE C.COLUMN_NAME IN ( 'Start_Time' , 'End_Time', 'Record_Type', 'User_Action')

), NumberedTables AS (

SELECT TABLE_NAME, ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS RowNum

FROM AgentTables

)

SELECT @SQL = @SQL + ' SELECT ''' + TABLE_NAME +''' AS AgentName, [Start_Time], [End_Time], [Record_Type] FROM ' + TABLE_NAME +

CASE WHEN RowNum = (SELECT COUNT(*) FROM NumberedTables) THEN ''

ELSE ' UNION ALL '

END

FROM NumberedTables

ORDER BY TABLE_NAME

-- PRINT @SQL;

EXEC (@SQL);

This may get you out of a hole for now, but fixing the normalization is the way to go.