查看: 4593|回复: 0

数据库中内容搜索，动态sql [复制链接]

zhaozl

新手上路

Rank: 1

电梯直达 go

楼主

发表于 2011-5-31 09:14:01 |只看该作者 |倒序浏览

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、检查有关键字的数据