--run with output set to Results To Text, copy and paste first results into new window to get the second results, etc.
SET NOCOUNT ON
DECLARE @UM VARCHAR(500)='EA'
SELECT 'IF EXISTS (SELECT TOP 1 1 FROM ' + col.TABLE_NAME + ' WHERE ' + col.COLUMN_NAME + ' = ''' + @UM + ''') PRINT ''SELECT ' + col.COLUMN_NAME + ' FROM ' + col.TABLE_NAME + ' WHERE ' + col.COLUMN_NAME + ' = ''''' + @UM + ''''' '';'
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN information_schema.TABLES tbl
ON tbl.TABLE_NAME = col.TABLE_NAME
AND tbl.table_schema = col.table_schema
AND tbl.table_catalog = col.table_catalog
AND tbl.table_type <> 'VIEW'
WHERE DOMAIN_NAME = 'UMType'
ORDER BY col.TABLE_NAME
SELECT 'IF EXISTS (SELECT TOP 1 1 FROM ' + col.TABLE_NAME + ' WHERE ' + col.COLUMN_NAME + ' = ''' + @UM + ''') PRINT ''SELECT ' + col.COLUMN_NAME + ' FROM ' + col.TABLE_NAME + ' WHERE ' + col.COLUMN_NAME + ' = ''''' + @UM + ''''' '';'
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN information_schema.TABLES tbl
ON tbl.TABLE_NAME = col.TABLE_NAME
AND tbl.table_schema = col.table_schema
AND tbl.table_catalog = col.table_catalog
AND tbl.table_type <> 'VIEW'
WHERE DOMAIN_NAME = 'WeightUnitsType'
ORDER BY col.TABLE_NAME
Comments