数据库寻找一个string所在的表

方法1偶尔会出错

[sql]
--方法1
declare @sSearchContent varchar(100)='f6f89834-817c-47d7-a436-6484754111cb'
-----------------------------------------------------------------------------------------------
DECLARE @Table TABLE(ID INT IDENTITY(1,1),columnName VARCHAR(100),tablename VARCHAR(100))
DECLARE @Line INT = 1
DECLARE @Total INT
DECLARE @Sql NVARCHAR(4000)
DECLARE @columnName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @num int

INSERT INTO @Table
SELECT A.name,OBJECT_NAME(A.object_id) AS tablename FROM SYS.COLUMNS AS A INNER JOIN SYSOBJECTS AS B ON A.object_id = B.id AND B.xtype = 'U' AND system_type_id = 167

SELECT @Total = COUNT(1) FROM @Table

WHILE @Line <= @Total
BEGIN
    SELECT @columnName = columnName,@TableName = tablename
    FROM @Table
    WHERE ID = @Line

    --set @sql='select @a=count(*) from ' + @TableName + ' WHERE ' + @columnName + ' = ''刘八'''
      set @sql='select @a=count(*) from ' + @TableName + ' WHERE ' + @columnName + ' = ''' + @sSearchContent + ''''
    exec sp_executesql @sql,N'@a int output',@num output

    IF @num > 0
    BEGIN
        SELECT @TableName AS TableName,@columnName AS ColumnName
    END

    SET @Line = @Line + 1
END
-----------------------------------------------------------------------------------------------

--方法2
declare @str varchar(100)
set @str='f6f89834-817c-47d7-a436-6484754111cb'
----------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------------------
作者：keenweiwei

补充：Web开发 , Jsp ,