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

------解决方案--------------------

SQL code
USE [数据库]
GO

DECLARE @what varchar(800)
SET @what='20110301' --要搜索的字符串

DECLARE @sql varchar(8000)

DECLARE TableCursor CURSOR LOCAL FOR
SELECT 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.types
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC( @sql )
    FETCH NEXT FROM TableCursor INTO @sql
END

CLOSE TableCursor

-- 删除游标引用
DEALLOCATE TableCursor

------解决方案--------------------

SQL code
--搜索指定的内容所在表的名称
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'