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

最新推荐文章于 2024-06-02 08:53:48 发布

ijwsoft

最新推荐文章于 2024-06-02 08:53:48 发布

阅读量927

点赞数

分类专栏：设计模式/算法可用类库、方法、DLL等链接导航资讯 Sql Server 文章标签： string 数据库 table sql output join

本文链接：https://blog.csdn.net/keenweiwei/article/details/7682666

版权

Sql Server 同时被 3 个专栏收录

125 篇文章 1 订阅

订阅专栏

可用类库、方法、DLL等

71 篇文章 0 订阅

订阅专栏

链接导航资讯

65 篇文章 0 订阅

订阅专栏

数据库寻找一个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='Housing Battery DEMO'
----------------------------------------------------------------------------------------------
 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 
     inner join sysobjects b on a.id=b.id 
     inner join sysusers c on c.uid=b.uid and c.name='dbo' -- 如果只要 dbo 用户下的表
     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


-------------------------------------------------------------------------------------------------------