[转] 数据库寻觅一个string所在的表-数据库教程-爱易网页

[转] 数据库寻觅一个string所在的表

日期：2014-05-16　浏览次数：20665 次

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

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

http://topic.csdn.net/u/20120619/14/2c920d2a-544f-4a91-ae23-953f5eb9893f.html?48501

方法1偶尔会出错

--方法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
----------------------------------------------------------

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

[转] 数据库寻觅一个string所在的表

相关资料更多>

推荐阅读更多>