mssql sqlserver 查找数据库存储某一值的列及表_方法分享


摘要:
客户近期提出一需求,需要帮忙检测数据库中包含 “****”值的表和列。
下文将讲述具体的实现方法及实现思路:



实现思路:
根据需查询的数据值,遍历所有的用户表和表对应的列,依次生成针对每张表的查询脚本,如果 存在值,则将表及列名插入到临时表中,
遍历完毕后,输出临时表。

例:

 create proc sp_findValue
    @type int,---数据类型 1文字 2数值  
    @str nvarchar(100)--需要值
as
    --创建临时表存放结果
    create table #tmp(keyId int identity ,tbName sysname,columnName sysname)
    declare @tbName nvarchar(300),@columnName sysname,@sql nvarchar(1000)
    if @type=1 
    begin
        declare curTable cursor fast_forward
        for 
            select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
            where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99)
     end
    else
    begin 
        declare curTable cursor fast_forward
        for 
        select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
            where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122)
    end
    open curtable
    fetch next from curtable into @tbName,@columnName
    while @@FETCH_STATUS=0
    begin
        set @sql='if exists (select * from '+@tbName+' where '
        if @type=1
        begin
            set @sql += @columnName + ' like ''%'+@str +'%'')'
        end
        else 
        begin
            set @sql +=@columnName + ' in ('+@str+'))'
        end

        set @sql += ' INSERT into #tmp(tbName ,columnName) values('''+@tbName+''','''+@columnName+''')'
        --print @sql
        exec (@sql)
        fetch next from curtable into @tbName,@columnName
    end
    close curtable 
    deallocate curtable
    select * from #tmp
    
    truncate table #tmp
    go
	-----------
	---应用举例:
	exec sp_findValue '1','查询值'