学步园

--Select '['+name+']' from master.dbo.sysdatabases order by name 数据库

declare @sele varchar(500)
    declare Ctable_name cursor for
   select table_name,column_name from information_schema.columns
    open Ctable_name
    declare @tablename varchar(50)
    declare @columnname varchar(50)
   fetch next from Ctable_name
       into @tablename,@columnname
    while (@@FETCH_STATUS = 0)
    begin
    print @tablename
   select  @sele = ' select  *  from '+ @tablename+
          '  where  '+ @columnname +' = N''0024'''
    exec (@sele)
   
        fetch next from Ctable_name
          into @tablename,@columnname
    end
    close Ctable_name
    deallocate Ctable_name

方法二： BAT或者Shell脚本，

v1=$1
sqlcmd -E -d trident1 -Q "SELECT 'SELECT '+''''+TABLE_NAME+'.'+COLUMN_NAME+''''+' AS NAME FROM dbo.'+TABLE_NAME+' WHERE CONVERT(varchar,['+COLUMN_NAME+'])='+''''+'$v1'+''''+' UNION ' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('int','bigint','smallint','tinyint','char','nvarchar','varchar')
UNION ALL SELECT TOP 1 'SELECT TOP 0 NAME FROM sys.objects' FROM sys.objects"|sed 's/([0-9]\{1,5\} rows affected)//' >temp.txt
attrib +H temp.txt
sqlcmd -E -d trident1 -i temp.txt
rm temp.txt

BAT 需要将V1=$1处改成Bat传参方法