数据库中内容搜索，动态sql

zhaozl

1、打开数据库，执行代码如下

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%'

3、检查有关键字的数据