我目前被要求验证全局搜索的结果，以查找包含一些关键字的所有记录跨整个数据库。为此，我需要检查每个表中具有关键字的所有行。这个全局搜索的结果已经准备好了，部分结果如下所示:
为此需要动态SQL。您可以将所有表一起生成一个大的UNION ALL查询。
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;
如果每个表有许多列或关键字，有更有效的方法可以做到这一点，但这应该是您开始的基础。
这个问题看起来很奇怪，你可以通过动态sql来完成，但不是作为一个常规的解决方案，更像是一次性或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
--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.