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

 

查询Employees这个表中的记录

 select * from dbo.Employees

 

Org_Id                                                                                                                                                                                                                                                             EmployeeId  EmployeeName                                       Title
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- -------------------------------------------------- --------------------------------------------------
0x                                                                                                                                                                                                                                                                 10000       陈希章                                                CEO
0x58                                                                                                                                                                                                                                                               10001       张三                                                 CTO
0x68                                                                                                                                                                                                                                                               10002       李四                                                 CFO
0x5AC0                                                                                                                                                                                                                                                             10003       王五                                                 IT Manager
0x5B40                                                                                                                                                                                                                                                             10004       赵六                                                 Manager
0x5AD6                                                                                                                                                                                                                                                             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 FOR
SELECT name from sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'
OPEN tabCursor
FETCH NEXT from tabCursor INTO @tabName
WHILE @@fetch_status = 0
BEGIN
set @tsql = ''
DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) and xtype=167
OPEN colCursor
FETCH NEXT from colCursor INTO @colName
WHILE @@fetch_status = 0
BEGIN
SET @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 @colName
END
exec(@tsql)
CLOSE colCursor
DEALLOCATE colCursor
FETCH NEXT from tabCursor INTO @tabName
END
CLOSE tabCursor
DEALLOCATE tabCursor

 

Org_Id                                                                                                                                                                                                                                                             EmployeeId  EmployeeName                                       Title
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- -------------------------------------------------- --------------------------------------------------
0x68                                                                                                                                                                                                                                                               10002       李四                                                 CFO

(1 行受影响)

TableName
---------
Employees

(1 行受影响)