怎样查找到已知记录的表名？
数据库类型：sql server 2008
数据库中有很多表
现在有一个字符型的值，比如说“业态”，我想查找到“业态”这个值在数据库中哪些表里有记录，应该怎样写这样的SQL语句。
[解决办法]
查找数据库中存在某个值的列。
alter proc spFind_Column_In_DB ( @type int,--类型：1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
[解决办法]
USE [数据库]GODECLARE @what varchar(800)SET @what='20110301' --要搜索的字符串DECLARE @sql varchar(8000)DECLARE TableCursor CURSOR LOCAL FORSELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段：['+o.name+'].['+c.name+']'''FROM syscolumns c JOIN sysobjects o ON c.id=o.id-- 175=char 56=int 可以查 select * from sys.typesWHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )OPEN TableCursorFETCH NEXT FROM TableCursor INTO @sqlWHILE @@FETCH_STATUS=0BEGIN EXEC( @sql ) FETCH NEXT FROM TableCursor INTO @sqlENDCLOSE TableCursor-- 删除游标引用DEALLOCATE TableCursor
[解决办法]
--搜索指定的内容所在表的名称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，模糊匹配