Thank you both for your assistance. I now have all of the code that I need to incorporate into QV.
I have to loop it through nine (9) servers. Any thoughts as to how it can be implemented?
//Build base table
SELECT s.name AS SchemaName
, t.name AS TableName
, c.name AS ColumnName
, 0 AS IsHashID
INTO #SchemaCrawler
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;
//Run a cursor
DECLARE @database
DECLARE @schema VARCHAR(15)
DECLARE @table VARCHAR(255)
DECLARE @column VARCHAR(255)
DECLARE @sql VARCHAR(MAX)
DECLARE LoopTables CURSOR FAST_FORWARD READ_ONLY FOR
SELECT s.name, t.name, c.name
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name = 'hashid'
AND t.name = 'plsubdatahash'
OPEN LoopTables
FETCH NEXT FROM LoopTables INTO @schema, @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'INSERT INTO #SchemaCrawler (SchemaName, TableName, ColumnName, IsHashID) SELECT DISTINCT ''' + @schema + ''' as SchemaName, ''' + @table + ''' as TableName, hashid AS ColumnName, 1 as IsHashID FROM ' + @schema + '.' + @table
EXEC sp_executesql @sql
FETCH NEXT FROM LoopTables INTO @schema, @table, @column
END
CLOSE LoopTables
DEALLOCATE LoopTables
SELECT *
FROM #SchemaCrawler
