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

2023-12-21 13:07:51

目标:在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)

文章来源:https://blog.csdn.net/jevonsflash/article/details/135127576
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。