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