SQL 查询数据库中包含指定字符串的相关表和相关记录

来源：互联网发布：Round it还是around it 编辑：程序博客网时间：2024/05/29 21:33

记录一下，以后备用；查询结果第一张表为包含字符串的相关表数据，第二章为表名，以此类推

/*查询数据库中包含指定字符串的数据表名及相应记录*/USE [数据库]GODECLARE @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

0 0