sqlserver中在指定数据库的所有表的所有列中搜索给定的值

比如：我们导入了某个客户的资料，我们知道此客户的姓名是zhangshan，我们想知道，在我们的业务数据库(eg:northwind)中，有哪些数据表的哪些字段设置了此姓名值zhangshan,通过下面的sql，我们就可以实现此目的，此处的sql搜索自网上，在此处做了局部修改。
一、搜索数据是string类型
适用于搜索text,ntext,varchar,nvarchar,char,nchar等类型
1、创建存储过程:my_search_stringingiventable

use [northwind]
go
/****** object: storedprocedure [dbo].[my_search_stringingiventable] script date: 09/25/2011 15:37:14 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[my_search_stringingiventable]
(@searchstring nvarchar(max),
@table_schema sysname,
@table_name sysname)
as
begin
declare @columns nvarchar(max), @cols nvarchar(max), @pkcolumn nvarchar(max)
-- get all character columns
set @columns = stuff((select ', ' + quotename(column_name)
from information_schema.columns
where data_type in ('text','ntext','varchar','nvarchar','char','nchar')
and table_name = @table_name
order by column_name
for xml path('')),1,2,'')
if @columns is null -- no character columns
return -1
-- get columns for select statement - we need to convert all columns to nvarchar(max)
set @cols = stuff((select ', cast(' + quotename(column_name) + ' as nvarchar(max)) as ' + quotename(column_name)
from information_schema.columns
where data_type in ('text','ntext','varchar','nvarchar','char','nchar')
and table_name = @table_name
order by column_name
for xml path('')),1,2,'')
set @pkcolumn = stuff((select n' + ''|'' + ' + ' cast(' + quotename(cu.column_name) + ' as nvarchar(max))'
from information_schema.table_constraints tc
inner join information_schema.constraint_column_usage cu on tc.table_name = cu.table_name
and tc.table_schema = cu.table_schema
and tc.constraint_name = cu.constraint_name
where tc.constraint_type ='primary key' and tc.table_schema = @table_schema and tc.table_name = @table_name
order by cu.column_name
for xml path('')),1,9,'')
if @pkcolumn is null
select @pkcolumn = 'cast(null as nvarchar(max))'
-- set select statement using dynamic unpivot
declare @sql nvarchar(max)
set @sql = 'select *, ' + quotename(@table_schema,'''') + 'as [table schema], ' + quotename(@table_name,'''') + ' as [table name]' +
' from
(select '+ @pkcolumn + ' as [pk column], ' + @cols + ' from ' + quotename(@table_name) +
' )src unpivot ([column value] for [column name] in (' + @columns + ')) unpvt
where [column value] like ''%'' + @searchstring + ''%'''
--print @sql
execute sp_executesql @sql, n'@searchstring nvarchar(max)', @searchstring
end

2、创建搜索存储过程:my_search_string_alltables
此存储过程将遍历指定数据库的所有表，并利用上面创建的存储过程my_search_stringingiventable来取得每个表的搜索结果。

use [northwind]
go
/****** object: storedprocedure [dbo].[my_search_string_alltables] script date: 09/25/2011 15:41:58 ******/
set ansi_nulls off
go
set quoted_identifier off
go
create proc [dbo].[my_search_string_alltables]
(
@searchstring nvarchar(max)
)
as
begin
create table #result ([pk column] nvarchar(max), [column value] nvarchar(max), [column name] sysname,
[table schema] sysname, [table name] sysname)
declare @table_name sysname, @table_schema sysname
declare curalltables cursor local forward_only static read_only
for
select table_schema, table_name
from information_schema.tables
where table_type = 'base table'
order by table_schema, table_name
open curalltables
fetch curalltables
into @table_schema, @table_name
while (@@fetch_status = 0) -- loop through all tables in the database
begin
insert #result
execute my_search_stringingiventable @searchstring, @table_schema, @table_name
fetch curalltables
into @table_schema, @table_name
end -- while
close curalltables
deallocate curalltables
-- return results
select * from #result order by [table name]
end

使用示例

use [northwind]
go
declare @return_value int
exec @return_value = [dbo].[my_search_string_alltables]
@searchstring = n'wantvalue'
select 'return value' = @return_value
go

还有另一个版本，就是直接创建一个存储过程来取得所要结果，但个人觉得前面那个方法更具灵活性

use [northwind]
go
/****** object: storedprocedure [dbo].[zl_searchalltables] script date: 09/25/2011 15:44:10 ******/
set ansi_nulls off
go
set quoted_identifier off
go
create proc [dbo].[zl_searchalltables]
(
@searchstr nvarchar(100)
)
as
begin
create table #results (columnname nvarchar(370), columnvalue nvarchar(3630))
set nocount on
declare @tablename nvarchar(256), @columnname nvarchar(128), @searchstr2 nvarchar(110)
set @tablename = ''
set @searchstr2 = quotename('%' + @searchstr + '%','''')
while @tablename is not null
begin
set @columnname = ''
set @tablename =
(
select min(quotename(table_schema) + '.' + quotename(table_name))
from information_schema.tables
where table_type = 'base table'
and quotename(table_schema) + '.' + quotename(table_name) > @tablename
and objectproperty(
object_id(
quotename(table_schema) + '.' + quotename(table_name)
), 'ismsshipped'
) = 0
)
while (@tablename is not null) and (@columnname is not null)
begin
set @columnname =
(
select min(quotename(column_name))
from information_schema.columns
where table_schema = parsename(@tablename, 2)
and table_name = parsename(@tablename, 1)
and data_type in ('char', 'varchar', 'nchar', 'nvarchar')
and quotename(column_name) > @columnname
)
if @columnname is not null
begin
insert into #results
exec
(
'select ''' + @tablename + '.' + @columnname + ''', left(' + @columnname + ', 3630)
from ' + @tablename + ' (nolock) ' +
' where ' + @columnname + ' like ' + @searchstr2
)
end
end
end
select columnname, columnvalue from #results
end
[code]
二、搜索数据是int类型
适用于搜索smallint, tinyint, int, bigint等类型
1、创建存储过程 my_search_intingiventable
[code]
use [northwind]
go
/****** object: storedprocedure [dbo].[my_search_intingiventable] script date: 09/25/2011 15:45:46 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[my_search_intingiventable]
(@searchvalue int,
@table_schema sysname,
@table_name sysname)
as
begin
declare @columns nvarchar(max) ,
@cols nvarchar(max) ,
@pkcolumn nvarchar(max) ,
@sql nvarchar(max)
--判断并创建#result表
if object_id('tempdb..#result', 'u') is not null
drop table #result
create table #result
(
[pk column] nvarchar(max) ,
[column value] bigint ,
[column name] sysname ,
[table schema] sysname ,
[table name] sysname
)
--开始搜索给定的表
declare curalltables cursor local forward_only static read_only
for
select table_schema ,
table_name
from information_schema.tables
where table_name =@table_name
open curalltables
while 1 = 1
begin
fetch curalltables
into @table_schema, @table_name
if @@fetch_status <> 0 -- loop through all tables in the database
break
print char(13) + 'processing ' + quotename(@table_schema) + '.'
+ quotename(@table_name)
-- get all int columns
set @columns = stuff(( select ', ' + quotename(column_name)
from information_schema.columns
where data_type like '%int'
and table_name = @table_name
and table_schema = @table_schema
order by column_name
for
xml path('')
), 1, 2, '')
if @columns is null
begin
print 'no int columns in the ' + quotename(@table_schema)
+ '.' + quotename(@table_name)
continue
end
-- get columns for select statement - we need to convert all columns to bigint
set @cols = stuff(( select ', cast(' + quotename(column_name)
+ ' as bigint) as '
+ quotename(column_name)
from information_schema.columns
where data_type like '%int'
and table_name = @table_name
order by column_name
for
xml path('')
), 1, 2, '')
-- create pk column(s)
set @pkcolumn = stuff(( select n' + ''|'' + ' + ' cast('
+ quotename(cu.column_name)
+ ' as nvarchar(max))'
from information_schema.table_constraints tc
inner join information_schema.constraint_column_usage cu on tc.table_name = cu.table_name
and tc.table_schema = cu.table_schema
and tc.constraint_name = cu.constraint_name
where tc.constraint_type = 'primary key'
and tc.table_schema = @table_schema
and tc.table_name = @table_name
order by cu.column_name
for
xml path('')
), 1, 9, '')
if @pkcolumn is null
select @pkcolumn = 'cast(null as nvarchar(max))'
-- set select statement using dynamic unpivot
set @sql = 'select *, ' + quotename(@table_schema, '''')
+ 'as [table schema], ' + quotename(@table_name, '''')
+ ' as [table name]' + ' from
(select ' + @pkcolumn + ' as [pk column], ' + @cols + ' from '
+ quotename(@table_schema) + '.' + quotename(@table_name)
+ ' )src unpivot ([column value] for [column name] in ('
+ @columns + ')) unpvt
where [column value] = @searchvalue'
--print @sql -- if we get errors, we may want to print generated sql
insert #result
( [pk column] ,
[column value] ,
[column name] ,
[table schema] ,
[table name]
)
execute sp_executesql @sql, n'@searchvalue int', @searchvalue
print 'found ' + cast(@@rowcount as varchar(10)) + ' records in '
+ quotename(@table_schema) + '.' + quotename(@table_name)
end
close curalltables
deallocate curalltables
select *
from #result
order by [table schema] ,
[table name]
end

2、创建搜索存储过程my_search_int_alltables,与上面类似，此存储过程将调用 my_search_intingiventable来实现所遍历的每一个数据表的搜索结果

use [northwind]
go
/****** object: storedprocedure [dbo].[my_search_int_alltables] script date: 09/25/2011 15:48:29 ******/
set ansi_nulls off
go
set quoted_identifier off
go
create proc [dbo].[my_search_int_alltables]
(
@searchvalue int
)
as
begin
create table #result ([pk column] nvarchar(max), [column value] nvarchar(max), [column name] sysname,
[table schema] sysname, [table name] sysname)
declare @table_name sysname, @table_schema sysname
declare curalltables cursor local forward_only static read_only
for
select table_schema, table_name
from information_schema.tables
where table_type = 'base table'
order by table_schema, table_name
open curalltables
fetch curalltables
into @table_schema, @table_name
while (@@fetch_status = 0) -- loop through all tables in the database
begin
insert #result
execute my_search_stringingiventable @searchvalue, @table_schema, @table_name
fetch curalltables
into @table_schema, @table_name
end -- while
close curalltables
deallocate curalltables
-- return results
select * from #result order by [table name]
end

使用示例

use [northwind]
go
declare @return_value int
exec @return_value = [dbo].[my_search_int_alltables]
@searchvalue = 68
select 'return value' = @return_value
go

note:
1、你可以根据上面一、二中的第1个存储过程来实现只搜索指定某些数据表的功能。
2、对于其它数据类型如:date，real等等均可以此为参照进行修改。
3、此方法对大型数据库会很耗时，所以尽量在小数据库上调试。当需要在大数据库上操作时，尽量避开数据库使用高峰时段并要有耐心。