如何在表的所有列中动态查找关键字

小编典典

如何在表的所有列中动态查找关键字

sql

我希望能够在表的所有列中动态查找特定的关键字。该脚本的目的是仅需更改关键字和表名，而无需知道表的所有列是什么。

这意味着以下代码不是令人满意的解决方案：

[…]输入“关键字”（字段1，字段2，字段3，字段4，…）[…]

我能够使用子查询动态检索表的所有列名。但是然后，该子查询中的IN语句返回空结果。

然后，我创建了一个新的子查询，该子查询以逗号分隔的列表格式返回列的列表。但是结果也是空的。

这是到目前为止的脚本：

DECLARE @DATABASE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)
DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @KEYWORD VARCHAR(128)
DECLARE @QUERY01 VARCHAR(MAX)
DECLARE @QUERY02 VARCHAR(MAX)
DECLARE @SUBQUERY01 VARCHAR(MAX)
DECLARE @SUBQUERY02 VARCHAR(MAX)

SET @DATABASE_NAME = '[xxx]'
SET @SCHEMA_NAME = '[dbo]'
SET @TABLE_NAME = 'tblCustomers'
SET @KEYWORD = 'Jean'

SET @SUBQUERY01 = 'SELECT Name FROM sys.columns WHERE object_id = (SELECT 
object_id FROM sys.tables WHERE name = ''' + @TABLE_NAME + ''')'
SET @SUBQUERY02 = 'SELECT SUBSTRING((SELECT '', '' + Name FROM 
sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE 
name = ''' + @TABLE_NAME + ''') FOR XML PATH('''')), 2, 1000000)'

SET @QUERY01 = 'SELECT * FROM ' + @DATABASE_NAME + '.' + @SCHEMA_NAME + 
'.' + @TABLE_NAME
-- + ' WHERE ''' + @KEYWORD + ''' IN (FirstName, LastName)' -- Works                    
+ ' WHERE ''' + @KEYWORD + ''' IN (' + @SUBQUERY01 + ')'

SET @QUERY02 = 'SELECT * FROM ' + @DATABASE_NAME + '.' + @SCHEMA_NAME + 
'.' + @TABLE_NAME
+ ' WHERE ''' + @KEYWORD + ''' IN (' + @SUBQUERY02 + ')'

EXEC (@SUBQUERY01)  -- OK (List of column names, one per row)
EXEC (@SUBQUERY02)  -- OK (List of column names, comma-separated values)

EXEC (@QUERY01)     -- NOK (Empty result)
EXEC (@QUERY02)     -- NOK (Empty result)

我希望脚本返回其中一列中包含关键字的所有行。例如，以下代码可以正常工作：

SELECT * FROM [xxx].[dbo].tblCustomers
WHERE 'Jean' IN (FirstName, LastName)

阅读 235

2021-04-14

共1个答案

小编典典

这可能是您所追求的。请注意，我将搜索限制为仅查看字符串类型的列，正确引用对象（使用QUOTENAME）并参数化以下语句：

DECLARE @Schema sysname,
        @Table sysname,
        @Keyword varchar(100);

DECLARE @SQL nvarchar(MAX);
SET @Schema = N'dbo';
SET @Table = N'icp_yyclient';
SET @Keyword = 'Smith';

SET @SQL = N'SELECT *' + NCHAR(13) + NCHAR(10) + 
           N'FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + NCHAR(13) + NCHAR(10) + 
           N'WHERE @Keyword IN (' + STUFF((SELECT N',' + QUOTENAME(C.[name])
                                           FROM sys.columns c
                                                JOIN sys.tables t ON c.object_id = t.object_id
                                                JOIN sys.schemas s ON t.schema_id = s.schema_id
                                                JOIN sys.types ct ON c.system_type_id = ct.system_type_id
                                            WHERE t.[name] = @Table
                                              AND s.[name] = @Schema
                                              AND ct.[name] IN (N'varchar',N'char',N'nvarchar',N'nchar',N'sysname')
                                            FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') + N');';
PRINT @SQL; --Your best friend

EXEC sp_executesql @SQL, N'@Keyword varchar(100)', @Keyword;

2021-04-14