SQL Server 在数据库中查找字符串（不知道表名的情况下 查找字符串）

查询Employees这个表中的记录

select * from dbo.Employees

Org_Id                                                                                                                                                                                                                                                             EmployeeId  EmployeeName                                       Title------------------------------------------------------------------------------------------------------------------ ----------- -------------------------------------------------- --------------------------------------------------0x                                                                                                                                                                                                                                                                 10000       陈希章                                                CEO0x58                                                                                                                                                                                                                                                               10001       张三                                                 CTO0x68                                                                                                                                                                                                                                                               10002       李四                                                 CFO0x5AC0                                                                                                                                                                                                                                                             10003       王五                                                 IT Manager0x5B40                                                                                                                                                                                                                                                             10004       赵六                                                 Manager0x5AD6                                                                                                                                                                                                                                                             10005       洪七                                                 Employee(6 行受影响)

测试

同过下面的T-SQL查询 李四 在哪个表中的如下

declare @key varchar(30)set @key = '李四' --替换为要查找的字符串DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)DECLARE @sql VARCHAR(2000)declare @tsql varchar(8000)DECLARE tabCursor CURSOR FORSELECT name from sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'OPEN tabCursorFETCH NEXT from tabCursor INTO @tabNameWHILE @@fetch_status = 0BEGINset @tsql = ''DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) and xtype=167OPEN colCursorFETCH NEXT from colCursor INTO @colNameWHILE @@fetch_status = 0BEGINSET @sql = 'if(exists(select * from ' + @tabName + ' where 'SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from 'set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''+ @tabName + ''' as TableName end'set @tsql = @tsql + @sql + ';'FETCH NEXT from colCursor INTO @colNameENDexec(@tsql)CLOSE colCursorDEALLOCATE colCursorFETCH NEXT from tabCursor INTO @tabNameENDCLOSE tabCursorDEALLOCATE tabCursor

Org_Id                                                                                                                                                                                                                                                             EmployeeId  EmployeeName                                       Title------------------------------------------------------------------------------------------------------------------ ----------- -------------------------------------------------- --------------------------------------------------0x68                                                                                                                                                                                                                                                               10002       李四                                                 CFO(1 行受影响)TableName---------Employees(1 行受影响)