sql server查询数据库中包含指定字符串的数据表

 发布一个实用小工具，可以很方便的在数据库中找到包含指定字符串的数据表名及相应记录：
/*
功能：查询数据库中包含指定字符串的数据表名及相应记录
作者：陈加鹏 chjpeng#163.com
日期：2009-08-17
*/
declare @key varchar(30)
set @key = 'test'    --替换为要查找的字符串
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