[学习笔记]SQL Server中批量查找所有符合Where条件的记录

目标：在SQL Server中查找所有表的UserId = 50的记录

创建一个表变量来存储所有包含’UserId’列的表的名称。然后使用一个游标遍历这些表，并对每个表执行一个动态SQL查询

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @ColumnName = N'UserId'
SET @SearchStr2 = N'50'

DECLARE @Tables TABLE(
    TABLE_NAME nvarchar(256),
    COLUMN_NAME nvarchar(128),
    BUFFER nvarchar(max)
)

INSERT INTO @Tables
SELECT QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME), COLUMN_NAME, ''
FROM INFORMATION_SCHEMA.TABLES AS T
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE C.COLUMN_NAME LIKE '%' + @ColumnName + '%' 
AND TABLE_TYPE = 'BASE TABLE'

DECLARE TableCursor CURSOR FOR 
SELECT TABLE_NAME, COLUMN_NAME FROM @Tables

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @SearchStr2)
    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName
END

CLOSE TableCursor
DEALLOCATE TableCursor

运行Sql脚本，可以看到所有包含’UserId’列的表的记录都被筛选出来了

这里筛选的表是包含’UserId’列的表，例如’CreationUserId’也会参与后续匹配，如果你想要筛选所有的表，可以把查询条件改成：

WHERE C.COLUMN_NAME =  @ColumnName  

如果查询结果想带上表名称，在动态SQL查询现在返回一个名为TableName的额外列即可

EXEC('SELECT ''' + @TableName + ''' AS TableName, * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @SearchStr2)