22,210
社区成员
发帖
与我相关
我的任务
分享遍历数据库查找一个关键字
我有一个数据库90张表， 我知道一个关键字，但是我不知道它在哪个表里面，不知道能不能通过某种查询方法来查出那几张表里有这个关键字。返回 表名和字段名 如果解决 分不够可以再加。
...全文
请发表友善的回复…
发表回复
sunshinebbs 2012-09-06
- 打赏
- 举报
我也正在找解决方法,谢谢分享~~~~~~~~~~~~~~~~~
Amoy狼 2011-05-16
- 打赏
- 举报
我需要，怎么看不到3楼的呢
zhuhaizengyuelin 2010-04-26
- 打赏
- 举报
谢谢大家对小弟的帮助，我将各位的代码使用了一下。都有用，但是效果有些差异，三楼的代码最少却实现了我的要求，这个我很喜欢，分不多 大家别介意！ 希望大家能以后的时间合作1
dawugui 2010-04-24
- 打赏
- 举报
转自CSDN--ojuju10
已知表和字段，用like去模糊查询一个字符串，这个比较简单，但是只已知一个字符串，去查询整个数据库内有那些表里面包含这个字符串，这个问题就不是那么简单啦！通过查询了大量的资料，最后用游标实现了这个需求：
create proc Full_Search(@string varchar(50))
as
begin
declare @tbname varchar(50)
declare tbroy cursor for select name from sysobjects
where xtype='u' --第一个游标遍历所有的表
open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin
declare @colname varchar(50)
declare colroy cursor for select name from syscolumns
where id=object_id(@tbname) and xtype in (
select xtype from systypes
where name in ('varchar','nvarchar','char','nchar') --数据类型为字符型的字段
) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段
open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin
declare @sql nvarchar(1000),@j int
select @sql='select @i=count(1) from ' +@tbname +' where '+ @colname+' like '+'''%'+@string+'%'''
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数
if @j>0
exec('select distinct '+@colname+' from ' +@tbname +' where '+ @colname+' like '+'''%'+@string+'%''')
fetch next from colroy into @colname
end
close colroy
deallocate colroy
fetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
end
exec Full_Search '市场'
drop proc Full_Search
东那个升 2010-04-23
- 打赏
- 举报
if exists(select * from sys.objects where object_id = object_id('dbo.proc_search_for_text') and type = 'P')
drop procedure dbo.proc_search_for_text
go
create procedure dbo.proc_search_for_text
@text varchar(max),
@db_name sysname = NULL,
@sel_table_name sysname = NULL
as
begin
set nocount on
----------------------------------------------------------------------------
-- check parameters
if @text is NULL
begin
raiserror('Text is null', 16, 1)
return
end
if @text = ''
begin
raiserror('Text is empty', 16, 1)
return
end
if @db_name is not null
begin
if not exists(select * from master.sys.databases where name = @db_name and name not in ('tempdb'))
begin
raiserror('Database does not exist or can not be scanned', 16, 1)
return
end
end
------------------------------------------------------------------------------------------------
-- get databases list
create table #temp_dbs_table
(
db_name sysname not null primary key
)
insert into #temp_dbs_table (db_name) select name from master.sys.databases where name not in ('tempdb') and (@db_name is null or (@db_name is not null and @db_name = name))
declare @current_db_name sysname
set @current_db_name = N''
create table #temp_columns_table
(
table_id int not null,
schema_name sysname not null,
table_name sysname not null,
column_name sysname not null,
mod_flag tinyint not null default 1
primary key(schema_name, table_name, column_name)
)
while @current_db_name is not null
begin
set @current_db_name = NULL
-----------------------------------------------------------------------------------------------
-- move to next database
select top 1 @current_db_name = db_name from #temp_dbs_table
if @current_db_name is null
break
---------------------------------------------------------------------------------------------
-- get columns list
truncate table #temp_columns_table
declare @n_cmd nvarchar(max)
if @sel_table_name is NULL
begin
set @n_cmd = N'insert into #temp_columns_table
select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
from [' + @current_db_name + '].sys.schemas as ss inner join
[' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join
[' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */)
order by ss.name, st.name, sc.name'
end
else
begin
set @n_cmd = N'insert into #temp_columns_table
select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
from [' + @current_db_name + '].sys.schemas as ss inner join
[' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join
[' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */)
and st.name = ''' + @sel_table_name + ''' order by ss.name, st.name, sc.name'
end
-- print @n_cmd
exec sp_executesql @n_cmd
----------------------------------------------------------------------------------
-- cycle through columns
declare @n_sec_cmd nvarchar(max)
declare @n_thi_cmd nvarchar(max)
declare @schema_name sysname
declare @table_name sysname
declare @column_name sysname
declare @table_id int
declare @prev_table_id int
set @table_id = 0
set @prev_table_id = -1
while @table_id >= 0
begin
set @table_id = NULL
select top 1
@table_id = table_id,
@schema_name = schema_name,
@table_name = table_name,
@column_name = column_name
from
#temp_columns_table
where
mod_flag <> 0
order by
schema_name,
table_name,
column_name
if @table_id is NULL
set @table_id = -1
if @table_id <> @prev_table_id
begin
-------------------------------------------------------------------------------------
-- execute previous command
if @prev_table_id > 0
begin
set @n_thi_cmd = 'if exists (' + @n_sec_cmd + ') ' + char(13)
+ 'begin ' + char(13)
+ 'select '''
+ @current_db_name + ''' as database_name, '''
+ @schema_name + ''' as schema_name, '''
+ @table_name + ''' as table_name'
+ char(13)
+ @n_sec_cmd + char(13) + 'end'
print @n_thi_cmd
exec sp_executesql @n_thi_cmd
end
set @n_sec_cmd = 'select * from [' + @current_db_name + '].[' + @schema_name + '].[' + @table_name + '] where ([' + @column_name + '] is not null and [' + @column_name + '] like ''%' + @text + '%'') '
end
else
begin
set @n_sec_cmd = @n_sec_cmd + char(13) + ' or ([' + @column_name + '] is not null and replace(upper(cast([' + @column_name + '] as varchar(64))), ''-'', '''' ) like ''%' + @text + '%'') '
end
set @prev_table_id = @table_id
update #temp_columns_table set mod_flag = 0 where table_id = @table_id and column_name = @column_name
end
delete from #temp_dbs_table where db_name = @current_db_name
end
---------------------------------------------------------------------------------
-- cleanup
drop table #temp_columns_table
drop table #temp_dbs_table
end
go
-- examples:
use master
go
create table tba( name varchar (100))
go
insert tba select 'dongsheng'
go
exec dbo.proc_search_for_text @text='dongsheng',@db_name='master'http://www.sqlservercentral.com/scripts/Miscellaneous/62066/
--小F-- 2010-04-23
- 打赏
- 举报
------------------第一种方法----------------------
CREATE PROC sp_ValueSearch
@value sql_variant, --要搜索的数据
@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN
--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')
--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
ELSE N'=@value' END
ELSE
BEGIN
SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
IF @sql LIKE N'%char' or @sql LIKE N'%text'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%char' or name LIKE N'%text'
SELECT @sql=N' LIKE N''%''+CAST(@value as '
+CASE
WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
ELSE 'varchar(8000)' END
+N')+N''%'''
END
ELSE IF @sql LIKE N'%datetime'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%datetime'
SET @sql=N'=@value'
END
ELSE IF @sql LIKE N'%int'
OR @sql LIKE N'%money'
OR @sql IN(N'real',N'float',N'decimal',N'numeric')
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%int'
OR name LIKE N'%money'
OR name IN(N'real',N'float',N'decimal')
SET @sql=N'=@value'
END
ELSE
SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))
DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+QUOTENAME(o.name)
+N' WHERE '+QUOTENAME(c.name)
+@sql,
N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE
WHEN t.name IN (N'decimal',N'numeric')
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
WHEN t.name=N'float'
OR t.name like N'%char'
OR t.name like N'%binary'
THEN N'('+CAST(c.prec as varchar)+N')'
ELSE N'' END,N'''')
+N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
AND c.xusertype=t.xusertype
AND t.xtype=tt.xtype
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #
exec sp_ValueSearch '要搜索的值',1 --1或不输入（即默认值1）精确匹配
exec sp_ValueSearch '要搜索的值',0 --不等于1，模糊匹配
---------------第二种方法------------------
Create PROC xb_GetTableNameAndColNameForValue
@value varchar(200)
AS
--求test库中包含值为@value的表和列名
--存储表名和列名
IF object_id('tabss') IS NOT NULL
exec('drop table tabss')
CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))
--查询某表某列是否包含某个值
IF object_id('ysgs') IS NOT NULL
exec('drop proc ysgs')
exec('create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')
--将结果存入tabss表中
EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0 insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'
GO
/*调用
exec xb_GetTableNameAndColNameForValue 'aa_1'
SELECT * FROM tabss
*/
------------------------第三种方法---------------------
declare @name nvarchar(100)
declare cur cursor for select name from sysobjects where type = 'U'
open cur
fetch next from cur into @name
WHILE @@FETCH_STATUS = 0
begin
declare @sql nvarchar(500),@s varchar(500)
set @s =''
set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) '
exec sp_executesql @sql,N'@s varchar(500) out',@s out
if len(@s) > 0
exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''')
fetch next from cur into @name
end
close cur
DEALLOCATE cur
------第4种方法---------（来自小梁）
CREATE TABLE tb(id int,col varchar(20))
CREATE TABLE tb2(id int,data varchar(20))
INSERT tb VALUES(1,'中国');
INSERT tb VALUES(2,'liangck')
INSERT tb2 VALUES(1,'China')
GO
CREATE PROCEDURE dbo.FindString
@string NVARCHAR(100)
AS
DECLARE @SQL NVARCHAR(4000);
SET @SQL = N'
DECLARE @str NVARCHAR(4000);
SELECT
@str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%'
+ @string + ' %'''''',
c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
AND c.xtype=t.xtype
AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar'');
SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
CREATE TABLE #tb(a int);
INSERT #tb(a) EXEC(@str);
IF EXISTS(SELECT * FROM #tb)
PRINT ''?''
';
EXEC sp_MsforeachTable @SQL;
GO
EXEC dbo.FindString N'中国'
GO
DROP PROCEDURE dbo.FindString
DROP TABLE tb,tb2
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx
永生天地 2010-04-23
- 打赏
- 举报
我刚刚才看过上面这个
htl258_Tony 2010-04-23
- 打赏
- 举报
法二：只包含char,nchar,varchar,nvarchar
---------------------------
declare @str varchar(100)
set @str='a' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tbhtl258_Tony 2010-04-23
- 打赏
- 举报
搜索指定数据在那个对象中存在.txt
CREATE PROC sp_ValueSearch
@value sql_variant, --要搜索的数据
@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN
--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')
--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
ELSE N'=@value' END
ELSE
BEGIN
SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
IF @sql LIKE N'%char' or @sql LIKE N'%text'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%char' or name LIKE N'%text'
SELECT @sql=N' LIKE N''%''+CAST(@value as '
+CASE
WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
ELSE 'varchar(8000)' END
+N')+N''%'''
END
ELSE IF @sql LIKE N'%datetime'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%datetime'
SET @sql=N'=@value'
END
ELSE IF @sql LIKE N'%int'
OR @sql LIKE N'%money'
OR @sql IN(N'real',N'float',N'decimal',N'numeric')
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%int'
OR name LIKE N'%money'
OR name IN(N'real',N'float',N'decimal')
SET @sql=N'=@value'
END
ELSE
SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))
DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+QUOTENAME(o.name)
+N' WHERE '+QUOTENAME(c.name)
+@sql,
N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE
WHEN t.name IN (N'decimal',N'numeric')
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
WHEN t.name=N'float'
OR t.name like N'%char'
OR t.name like N'%binary'
THEN N'('+CAST(c.prec as varchar)+N')'
ELSE N'' END,N'''')
+N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
AND c.xusertype=t.xusertype
AND t.xtype=tt.xtype
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #
GO永生天地 2010-04-23
- 打赏
- 举报
declare @str varchar(100)
set @str='a' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
