數據庫尋找一個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
