如何使用循环进行类似或Ilike功能？

Question

目前，我被要求验证全局搜索的结果，该结果以查找包含某些关键字的所有记录交叉整个数据库。为此，我需要检查每个具有关键字的表中的所有行。此全局搜索的结果已准备就绪，部分结果看起来像下面的结果：

table_name	column_name	关键字	cnt
wf_process	name_en	fec	11
wf_process	ftablename	gb	14
iccclass	name_en	gb	4

我试图使用的是使用“喜欢”操作员提取'每个表中包含关键字的列中的所有数据。也就是说，我将使用以下查询：

select distinct Name_EN, FTABLENAME from Wf_Process where Name_EN like '%FEC%' or FTABLENAME like '%GB%'

并

select distinct Name_EN from ICCClass where Name_EN like '%GB%'

删除整个数据库中所需的所有数据。如果我只有三个记录，那不是问题。但是，我的结果返回了数千个表格，总共包含10K行。因此，我试图使用循环来执行此任务，但我失败了。

因此，我的问题是，有人有任何想法写一个循环来一次搜索所有表吗？还是有其他方式而不是循环可以在SQL Server中执行此操作？

非常感谢！

Answer 1

您需要动态SQL。您可以一起生成一个大的联合所有查询所有表格。

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CAST('
SELECT
  ' + QUOTENAME(con.table_name, '''') + ' table_name,
  ' + QUOTENAME(con.column_name, '''') + ' column_name,
  ' + QUOTENAME(con.keyword, '''') + ' keyword,
  COUNT(*) cnt
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' t
WHERE t.' + QUOTENAME(c.name) + ' LIKE ' + QUOTENAME('%' + con.keyword + '%', '''')

       AS nvarchar(max)), '
UNION ALL')

    FROM YourConditions con
    JOIN sys.tables t ON t.table_name
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    JOIN sys.columns c ON c.object_id = t.object_id
    WHERE t.name = con.table_name
      AND c.name = con.column_name  
);

PRINT @sql;    -- your friend

EXEC sp_executesql @sql;

如果每个表有许多列或关键字，则有更有效的方法可以做到这一点，但这应该使您开始。

Answer 2

这个问题看起来很奇怪，您可以通过动态SQL完成，但不像一个常规解决方案更像一个或POC/POC/测试。

use tempdb
GO

drop table if exists search_result
go

create table search_result (
table_name sysname
,column_name sysname 
,keyword varchar(100))
go

insert into search_result values
('Wf_Process', 'Name_EN', 'FEC')
,('Wf_Process' , 'FTABLENAME', 'GB')
,('ICCClass', 'Name_EN', 'GB')
GO

drop table if exists result 
create table result (val varchar(500))
go

declare @col sysname
declare @tab sysname
declare @kw varchar(100)
declare @sql varchar(1000)

while exists (select * from search_result)
begin
    select top 1
        @tab = table_name
        , @col = column_name 
        , @kw = keyword
    from search_result

    set @sql = concat('insert into result select ', @col, ' from ', @tab, ' where ', @col, ' like ''%', @kw, '%''' )
    print(@sql)

    exec (@sql)

    delete from search_result
    where table_name = @tab and column_name = @col and keyword = @kw

end
GO

select * from result

Answer 3

--initially trying to get all the column name with its respective table name, finding out if there is that particular keyword in all table,
--if yes, insert into temp table, if there aint keyword, it will insert the record too but the count of record will be zero.
--trying to make it dynamic and running it inside the loop by assigning row number to each table with different column.

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP (TABLE_NAME NVARCHAR(200), COLUMN_NAME NVARCHAR(200), KEYWORD NVARCHAR(200), CNT INT)
DECLARE @TABLE NVARCHAR(200)
DECLARE @TABLE_ID INT
SET @TABLE_ID = 1
DECLARE @TABLE_NAME NVARCHAR (200)
DECLARE @FOR_LOOP INT
DECLARE @COLUMN NVARCHAR(200)
DECLARE @COLUMN_NAME NVARCHAR(200)
--TOTAL NO OF RECORDS FOR LOOP 
SET @FOR_LOOP = (SELECT COUNT(*) 
                    FROM SYS.tables T 
                    JOIN SYS.all_columns C ON T.object_id = C.object_id
                    JOIN INFORMATION_SCHEMA.TABLES S ON S.TABLE_NAME = T.name)
DECLARE @STRINGS NVARCHAR(200)
SET @STRINGS = '%FEC%' --------->ENTER YOUR KEYWORD HERE, TRY ONE AT A TIME 
DECLARE @COUNT INT
WHILE @TABLE_ID <= @FOR_LOOP
    BEGIN
    SET @TABLE = (SELECT CAST(TABLE_NAME AS NVARCHAR(200)) 
                    FROM
                        (SELECT ROW_NUMBER()OVER(ORDER BY T.NAME) TABLE_ID 
                        ,S.TABLE_SCHEMA SCHEMA_NAME ,T.NAME TABLE_NAME, C.name COLUMN_NAME
                        FROM SYS.tables T 
                        JOIN SYS.all_columns C ON T.object_id = C.object_id
                        JOIN INFORMATION_SCHEMA.TABLES S ON S.TABLE_NAME = T.name)A
                    WHERE TABLE_ID = @TABLE_ID)
    SET  @TABLE_NAME = '['+@TABLE+']'
    SET @COLUMN = (SELECT CAST(COLUMN_NAME AS NVARCHAR(200))
                    FROM
                        (SELECT ROW_NUMBER()OVER(ORDER BY T.NAME) TABLE_ID 
                        ,S.TABLE_SCHEMA SCHEMA_NAME ,T.NAME TABLE_NAME, C.name COLUMN_NAME
                        FROM SYS.tables T 
                        JOIN SYS.all_columns C ON T.object_id = C.object_id
                        JOIN INFORMATION_SCHEMA.TABLES S ON S.TABLE_NAME = T.name)A
                    WHERE TABLE_ID = @TABLE_ID)
    SET @COLUMN_NAME = '['+@COLUMN+']'
    DECLARE @EXEC NVARCHAR(200) = 'SELECT ' + @COLUMN_NAME +' FROM ' + @TABLE_NAME + ' WHERE ' + @COLUMN_NAME + ' LIKE ' + ''''+@STRINGS+''''
    --THERE MUST BE ANOTHER WAY TO REPLACE SP_EXECUTESQL FOR BETTER PERFORMANCE, AS IT WILL BE EXECUTED AS MANY TIMES AS THERE ARE RECORDS IN @FOR_LOOP 
    EXEC SP_EXECUTESQL @EXEC
    SET @COUNT = (SELECT @@ROWCOUNT)
    IF (SELECT @@ROWCOUNT) >=1
    BEGIN
        INSERT INTO #TEMP VALUES 
        (@TABLE_NAME, @COLUMN_NAME, @STRINGS, @COUNT)
        SET @TABLE_ID = @TABLE_ID + 1
    END
    ELSE
    --DONT KNOW WHY THIS ELSE PART IS NOT TOUCHED, WHEREAS I THINK IT SHOULD HAVE
    BEGIN
        PRINT 'RECORD NOT FOUND'
    END
END
GO

--AFTER THE ABOVE BLOCK IS EXECUTED THEN TRY RUNNING BELOW ONE
SELECT *FROM #TEMP WHERE CNT>0

--THERE MAY BE MULTIPLE ERROS, MUST BE EDITED AND CAN MAKE IT AS A PROCEDURE TOO.