不显示空返回集 [英] Don't display empty return sets

问题描述

我有以下sql代码:

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

对于给定的数据库，它会查看所有具有名为 columnOfInterest 且包含值 123 的列的表.这是出于调试目的，因此输出不必很漂亮，当然也不是.大多数表都没有列，其中甚至更少的表中的值为 123，因此大多数返回集都是空的.
我正在寻找整理输出的方法.例如，我根本不需要看到空集.关于如何执行此操作或以其他方式清理它的任何想法?
附件是 SQL Server 管理中输出的屏幕截图.我不得不删除一些可能敏感的列名.蓝色列是 columnOfInterest、黑色和所有其他列.您在此处看到的每一行都是 1 个表的空输出.

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.

推荐答案

将动态查询包装在 IF 块中，用于测试查询是否会返回任何内容.

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

这篇关于不显示空返回集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！