用于人工分析数据库中数据依赖关系
以下sql语句修改完
SET @SearchTxt=’此处输入要查询的内容’
后直接全部执行即可。
--查询的条件
DECLARE @SearchTxt AS NVARCHAR(100)
SET @SearchTxt='此处输入要查询的内容'
--定义表变量结构
DECLARE @info TABLE
(
TableName NVARCHAR(100)
,ColumnName NVARCHAR(100)
)
DECLARE @TableName AS NVARCHAR(100)
DECLARE @ColumnName AS NVARCHAR(100)
DECLARE @SQLSTR AS NVARCHAR(1000)
DECLARE @Result AS BIT
--定义游标 获取所有表的所有列
DECLARE X CURSOR FOR
SELECT b.name as 表名,a.name as 列名
FROM sys.columns a
JOIN sys.tables b on a.object_id=b.object_id
--WHERE user_type_id = 127 --此处可以添加数据类型,如果知道类型精确类型匹配查找
--打开游标
OPEN X
FETCH NEXT FROM X INTO @TableName,@ColumnName
WHILE (@@FETCH_STATUS=0)
BEGIN
--拼接查询SQL,如果想做包含查询 此处 改为 LIKE 拼接即可
SET @SQLSTR = N'SELECT * FROM ' + @TableName
+ ' WHERE [' + @ColumnName + '] = ''' + @SearchTxt + ''''
SET @SQLSTR = 'SET @Result = CASE WHEN EXISTS('+ @SQLSTR +') THEN 1 ELSE 0 END'
PRINT @SQLSTR
--执行动态SQL
EXECUTE SP_EXECUTESQL @SQLSTR ,@params = N' @Result BIT out ',@Result=@Result out
--判断是否存在,插入表变量
IF (@Result='1')
BEGIN
INSERT INTO @info VALUES (@TableName,@ColumnName)
END
FETCH NEXT FROM X INTO @TableName,@ColumnName
END
CLOSE X
DEALLOCATE X
--输出查询结果
SELECT * FROM @info