Don't display empty return sets

问题:

I have the following sql code:

select 'select * from ' + table_name + ' where ' + column_name + ' = 123'
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE COLUMN_NAME like '%columnOfInterest%'

For a given database, this looks through all the tables that have a column named columnOfInterest which contain a value 123. This is for debug purposes and so the output doesn't have to be pretty and it certainly is not. Most of the tables don't have a column and of those even fewer have a value of 123 in them, so most of the return sets are empty.
I'm looking for ways to neaten the output. For example, I don't need to see empty sets at all. Any ideas on how to do this or clean it up otherwise?
Attached is a screenshot of the output in SQL Server Manage. I've had to erase some potentially sensitive column names. The blue columns are the columnOfInterest, the black, all others. Each row you see here is the empty output from 1 table.

回答1:

wrap the dynamic query in a IF block that tests if the query will return anything.

select 'if exists (' + QUERY + ')' + char(10) + '    ' + QUERY
from (
    select QUERY = 'select * from ' + TABLE_NAME 
        + ' where ' + COLUMN_NAME + ' = 123'
    from INFORMATION_SCHEMA.COLUMNS  
    where COLUMN_NAME like '%columnOfInterest%'
)  q

回答2:

This should return the table names with column name. Will this do?

DECLARE @Tsql NVARCHAR(MAX)
SET @Tsql = ''
select @Tsql = @Tsql +  'select TOP(1) ''' + c.table_schema + '.' + c.table_name + ''' AS TableName, ''' + c.COLUMN_NAME + ''' AS ColumnName  from ' + c.table_schema + '.' + c.table_name + ' where EXISTS 
    (SELECT 1 FROM ' + c.table_schema + '.' + c.table_name + ' WHERE ' + c.column_name + ' = 123) UNION' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_NAME like '%SomeColumnName%'
--You may want to filter on data type here.  Did I leave anything out?
AND c.data_type IN ('BIGINT', 'INT', 'TINYINT', 'SMALLINT', 'NUMBER', 'DECIMAL', 'MONEY')

SELECT @Tsql = LEFT(@Tsql, LEN(@Tsql) - 7)

PRINT @Tsql

--EXEC sp_executesql @Tsql