use Shop_Release
declare @keyword varchar(20)
set @keyword = 'zhaozl'
--创建表
if exists(select * from sysobjects where id = object_id(N'[dbo].[TempColums_1_x_1]') and type in (N'U'))
drop table TempColums_1_x_1
create table TempColums_1_x_1(ColumnName varchar(100),TableName varchar(100))
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fun_CheckKeyWord_1_x_1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].fun_CheckKeyWord_1_x_1
declare @sqlStr nvarchar(4000)
set @sqlStr=
'
create function [dbo].[fun_CheckKeyWord_1_x_1](@tableName varchar(100),@keyword varchar(20)) returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str = ''''
select @str = @str + '' or lower(['' + cast(ColumnName as varchar) +'']) like ''''%''+lower(@keyword)+''%''''''
from TempColums_1_x_1
where TableName = @tableName
if(len(@str)) > 0
set @str = right(@str , len(@str) - 3)
return @str
end
'
execute(@sqlStr)
--表
set @sqlStr = 'select Name into #TempTable from sysobjects where xtype=''U'';'
--列
set @sqlStr = @sqlStr+ 'insert into TempColums_1_x_1'
+ ' select A.name as ColumnName, C.name as TableName '
+ ' from syscolumns A, systypes B, sysobjects C'
+ ' where B.name in(''varchar'',''nvarchar'',''char'',''nchar'') and A.xtype = B.xtype and A.id = C.id and C.xtype=''U'' and B.name != ''sysname'' and C.name in(select name from #TempTable)'
+ ' and C.name != ''TempColums_1_x_1'';'
--结果sql
set @sqlStr = @sqlStr+ ' select +''select '''''' + name +'''''' as TableName,* from '' + name +'' where''+ dbo.[fun_CheckKeyWord_1_x_1](name,'''+@keyword+''') from #TempTable where dbo.[fun_CheckKeyWord_1_x_1](name,'''+@keyword+''') != '''';';
/*
if exists(select * from sysobjects where id = object_id(N'[dbo].[TempTable_1_x_1]') and type in (N'U'))
drop table TempTable_1_x_1
create table TempTable_1_x_1(c_sql varchar(7000),c_tablename varchar(100),c_where text,c_hasData bit)
set @sqlStr = @sqlStr+ 'insert into TempTable_1_x_1 '
+' select +''select '''''' + name +'''''' as TableName,* from '' + name +'' where''+ dbo.[fun_CheckKeyWord_1_x_1](name,'''+@keyword+''') as c_sql, '
+' name as c_tablename, '
+' dbo.[fun_CheckKeyWord_1_x_1](name,'''+@keyword+''') as c_where, '
+' 0 as c_hasData '
--+' into #TempTableData '
+' from #TempTable where dbo.[fun_CheckKeyWord_1_x_1](name,'''+@keyword+''') != '''';';
--set @sqlStr = @sqlStr+ ' select * from TempTable_1_x_1;';
*/
print @sqlStr
exec sp_executesql @sqlStr
if exists(select * from sysobjects where id = object_id(N'[dbo].[TempColums_1_x_1]') and type in (N'U'))
drop table TempColums_1_x_1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fun_CheckKeyWord_1_x_1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].fun_CheckKeyWord_1_x_1
2、将执行结果在新的sql分析器中执行
select 'adminloginlog' as TableName,* from adminloginlog where lower([adminname]) like '%zhaozl%' or lower([password]) like '%zhaozl%' or lower([loginip]) like '%zhaozl%' or lower([hostcomputername]) like '%zhaozl%' or lower([operatenote]) like '%zhaozl%'
select 'advertisemanage' as TableName,* from advertisemanage where lower([name]) like '%zhaozl%' or lower([upspreadadd]) like '%zhaozl%' or lower([sizebreadth]) like '%zhaozl%' or lower([hight]) like '%zhaozl%' or lower([linkaddress]) like '%zhaozl%' or lower([hint]) like '%zhaozl%' or lower([advertisecont]) like '%zhaozl%'
select 'product_sendgroupbuyinfo' as TableName,* from product_sendgroupbuyinfo where lower([address]) like '%zhaozl%' or lower([title]) like '%zhaozl%' or lower([synopsis]) like '%zhaozl%' or lower([images]) like '%zhaozl%' or lower([createip]) like '%zhaozl%' or lower([activityqq]) like '%zhaozl%' or lower([tel]) like '%zhaozl%' or lower([idcard]) like '%zhaozl%' or lower([name]) like '%zhaozl%'
select 'productalbum' as TableName,* from productalbum where lower([thumbnailaddress]) like '%zhaozl%' or lower([originaladdress]) like '%zhaozl%' or lower([descriptoin]) like '%zhaozl%'
select 'article' as TableName,* from article where lower([Channel]) like '%zhaozl%' or lower([Title]) like '%zhaozl%' or lower([SubTitle]) like '%zhaozl%' or lower([KeyWord]) like '%zhaozl%' or lower([CopyFrom]) like '%zhaozl%' or lower([LinkUrl]) like '%zhaozl%' or lower([Author]) like '%zhaozl%' or lower([Users]) like '%zhaozl%' or lower([Editor]) like '%zhaozl%' or lower([Area]) like '%zhaozl%' or lower([Property]) like '%zhaozl%' or lower([ImagesAddress]) like '%zhaozl%'