|
SQL Server全库搜索(在所有表中查找内容)
SQL Server版本:SQL Server2008
某个内容到底存储在数据库的哪个地方?无从下手时,可以使用全库查找。
SQL Server在整个库的所有表的所有字段中查找内容,用到了临时表,游标循环。
- declare @Str nvarchar(max), @tableName varchar(50), @colName varchar(50), @rowCount int
- select a.name tableName, b.name Colname, 0 as IsFound into #t1
- from sysobjects a join syscolumns b on a.id=b.id join systypes c on b.xtype=c.xtype
- where a.[type]=‘U‘ and c.name in (‘varchar‘, ‘nvarchar‘, ‘char‘, ‘nchar‘) --这里是设置字段的类型,以缩小范围
- declare _c1 cursor for select Colname, tableName from #t1
- open _c1
- fetch next from _c1 into @colName, @tableName
- while @@FETCH_STATUS=0 begin
- --print @Str
- select @Str=‘select @rowCount=count(1) from [‘+@tableName+‘] where [‘+@colName+‘] like ‘‘%TotalDsc%‘‘‘ --这里是要查找的内容
- exec sp_executesql @Str, N‘@rowCount int output‘, @rowCount output
- if @rowCount>0 update #t1 set IsFound=1 where ColName=@colName and tableName=@tableName
- fetch next from _c1 into @colName, @tableName
- end
- close _c1
- deallocate _c1
- select * from #t1 where IsFound=1
- drop table #t1
复制代码
搜索出来的结果解释:
tableName:表名
Colname:列名
IsFound:找到的个数
再也不怕找不到在数据库的哪个地方了。 |
|