遍历数据库查找一个关键字
我有一个数据库90张表， 我知道一个关键字，但是我不知道它在哪个表里面，不知道能不能通过某种查询方法来查出那几张表里有这个关键字。返回 表名和字段名 如果解决 分不够可以再加。
[解决办法]
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 tbfetch next from tb into @swhile @@fetch_status=0begin exec(@s) fetch next from tb into @sendclose tbdeallocate tb
[解决办法]
搜索指定数据在那个对象中存在.txtCREATE PROC sp_ValueSearch@value sql_variant, --要搜索的数据@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配ASSET NOCOUNT ONIF @value IS NULL RETURN--数据类型处理SELECT xtype INTO #t FROM systypesWHERE 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' ENDELSEBEGIN 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 LOCALFORSELECT 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 ttWHERE o.id=c.id AND c.xusertype=t.xusertype AND t.xtype=tt.xtype AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tbFETCH tb INTO @sql,@sql1WHILE @@FETCH_STATUS=0BEGIN 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,@sql1ENDCLOSE tbDEALLOCATE tbSELECT * FROM #GO
[解决办法]
法二：只包含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 tbfetch next from tb into @swhile @@fetch_status=0begin exec(@s) fetch next from tb into @sendclose tbdeallocate tb
[解决办法]
------------------第一种方法----------------------CREATE PROC sp_ValueSearch@value sql_variant, --要搜索的数据@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配ASSET NOCOUNT ONIF @value IS NULL RETURN--数据类型处理SELECT xtype INTO #t FROM systypesWHERE 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' ENDELSEBEGIN 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 LOCALFORSELECT 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 ttWHERE o.id=c.id AND c.xusertype=t.xusertype AND t.xtype=tt.xtype AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tbFETCH tb INTO @sql,@sql1WHILE @@FETCH_STATUS=0BEGIN 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,@sql1ENDCLOSE tbDEALLOCATE tbSELECT * 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))ASexec(''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 curfetch next from cur into @nameWHILE @@FETCH_STATUS = 0begin 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 @nameendclose curDEALLOCATE 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')GOCREATE 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;GOEXEC dbo.FindString N'中国'GODROP PROCEDURE dbo.FindStringDROP TABLE tb,tb2本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx
[解决办法]
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_textgocreate procedure dbo.proc_search_for_text @text varchar(max), @db_name sysname = NULL, @sel_table_name sysname = NULLasbegin 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_tableendgo-- examples:use mastergocreate table tba( name varchar (100))goinsert tba select 'dongsheng'goexec dbo.proc_search_for_text @text='dongsheng',@db_name='master'
[解决办法]
转自CSDN--ojuju10已知表和字段，用like去模糊查询一个字符串，这个比较简单，但是只已知一个字符串，去查询整个数据库内有那些表里面包含这个字符串，这个问题就不是那么简单啦！通过查询了大量的资料，最后用游标实现了这个需求： create proc Full_Search(@string varchar(50))asbegindeclare @tbname varchar(50) declare tbroy cursor for select name from sysobjects where xtype='u' --第一个游标遍历所有的表open tbroyfetch next from tbroy into @tbnamewhile @@fetch_status=0begin 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 colroyfetch next from tbroy into @tbnameendclose tbroydeallocate tbroyendexec Full_Search '市场'drop proc Full_Search