Sometimes, you need to find some data in a specificly named column, like what changed by a user, in the “LAST_CHANGED_BY” columns.
If you name the column, and the specific value you search, this script gives you a list of tables of how many rows any tables have with the matching values in matching columns.
DECLARE @columnname varchar(100) = 'LAST_CHANGED_BY'; DECLARE @columnvalue nvarchar(max) = 'USERNAME'; CREATE TABLE #TemporaryData ( TableName varchar(1000), RowNum int ); DECLARE @TablesToSearch TABLE ( ColumnName varchar(1000), TableName varchar(1000) ); INSERT INTO @TablesToSearch (ColumnName, TableName) SELECT c.name AS 'ColumnName' ,t.name AS 'TableName' FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name = @columnname; DECLARE @tablename nvarchar(max), @sql nvarchar(max); DECLARE table_cursor CURSOR FOR SELECT TableName FROM @TablesToSearch; OPEN table_cursor ; FETCH NEXT FROM table_cursor INTO @tablename ; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT #TemporaryData (TableName, RowNum) SELECT ''' + @tablename + ''', COUNT(1) FROM ' + @tablename + ' WHERE CAST(' + @columnname + ' as nvarchar(max)) = ''' + @columnvalue + '''; ' exec sp_executesql @sql; FETCH NEXT FROM table_cursor INTO @tablename END CLOSE table_cursor; DEALLOCATE table_cursor; SELECT * FROM #TemporaryData ORDER BY RowNum DESC; DROP TABLE #TemporaryData;