最近工作中遇到一个需求,要对整个数据库中每个表的每个字段进行条件查询。最后写了一段代码,实现遍历数据库的所有表并对每个字段进行条件查询。
下面的代码检查字段的值是否包含"http://",是则记录“表名字段名”。
环境:SQL SERVER 2008
代码:
use [database]--database为目标数据库名称
declare @sql nvarchar(1000)--记录操作执行语句
declare @tableid int
set @tableid=2147483647--最大int值
declare @tablename nvarchar(100)
set @tablename=''
while (@tablename is not null)
begin
set @tablename=(select top 1 name from sysobjects where id<@tableid and type in (N'u') order by id desc)
if @tablename is not null
begin
set @tableid=(select top 1 id from sysobjects where id<@tableid and type in (N'u') order by id desc)
declare @offset int
set @offset=2147483647
declare @columnName nvarchar(100)
set @columnName=''
while (@columnName is not null)
begin
set @columnName=(select top 1 name from syscolumns where offset<@offset and id=@tableid order by offset desc)
if @columnName is not null
begin
set @offset=(select top 1 offset from syscolumns where offset<@offset and id=@tableid order by offset desc)
set @sql='if exists (select top 1 '+@columnName+' from '+@tablename+' where '+@columnName+' like ''%http://%'') insert into [key_temp] ([key]) values (''[''+'''+@tablename+'''+'']''+'''+@columnName+''')'
exec(@sql)
end
end
end
end
测试功能正常,但性能一般。对性能没什么研究,请大虾赐教。