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

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

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

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

方法1偶尔会出错

--方法1declare @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 = 1DECLARE @Total INTDECLARE @Sql NVARCHAR(4000)DECLARE @columnName VARCHAR(100)DECLARE @TableName VARCHAR(100)DECLARE @num int INSERT INTO @TableSELECT 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 = 167SELECT @Total = COUNT(1) FROM @TableWHILE @Line &lt;= @TotalBEGIN    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 &gt; 0    BEGIN        SELECT @TableName AS TableName,@columnName AS ColumnName    END        SET @Line = @Line + 1END-------------------------------------------------------------方法2declare @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&gt;=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所在的表

热点推荐