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 行受影响)
