CREATE TABLE Test1 ( ID INT IDENTITY(1,1) NOT NULL, UserName VARCHAR(100) NOT NULL, Age INT )INSERT INTO Test1 SELECT 'A',12 UNION SELECT 'B',13 UNION SELECT 'C',14 UNION SELECT 'D',15DECLARE @TableColumn TABLE(Id INT IDENTITY(1,1),ColumnName VARCHAR(100)) DECLARE @TotalNum INT DECLARE @Line INT DECLARE @Count Int DECLARE @Sqls NVARCHAR(4000) DECLARE @ColumnName VARCHAR(100)SET @Line = 1INSERT INTO @TableColumn SELECT name FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'Test1'SELECT @TotalNum = MAX(ID) FROM @TableColumnWHILE @Line <= @TotalNum BEGIN SELECT @ColumnName = ColumnName FROM @TableColumn WHERE Id = @Line
set @Sqls='select @a=count(1) from Test1 WHERE ' + @ColumnName + '= ''15''' exec sp_executesql @Sqls,N'@a int output',@Count output
IF @Count > 0 BEGIN SELECT '数值所在列为:' + @ColumnName END SET @Line = @Line + 1 END若要遍历所有的表,只需要从Sysobjects中选出所有表遍历就可以了SELECT * FROM SYSOBJECTS WHERE type = 'U'
根据某个值,如何查詢到对应的表和字段及数据库如,給出值"798DACD1-8160-45FF-B3E4-9F4840578CDB",要求查詢出"798DACD1-8160-45FF-B3E4-9F4840578CDB"所在的表名,字段名,数据库名. --用游标遍历 declare @sql varchar(800) set @sql='798DACD1-8160-45FF-B3E4-9F4840578CDB' --要搜索的字符串 declare @sql varchar(8000) declare tablecursor cursor local for select sql='if exists ( select 1 from ['+ s.name + '].['+o.name+'] where ['+c.name+'] like ''%'+@sql+'%'' ) print ''所在的表及字段:['+o.name+'].['+c.name+']''' from sys.syscolumns c join sys.objects o on c.id=o.object_id join sys.schemas s on o.schema_id = s.schema_id where s.name != 'sys' and o.type='u' and c.status>=0 and c.xusertype in (167,175, 239, 231)open tablecursor fetch next from tablecursor into @sql while @@fetch_status=0 begin exec( @sql ) fetch next from tablecursor into @sql end close tablecursor deallocate tablecursor
把 3楼的 改一下就可以。这里引用一下3楼的。 用like CREATE TABLE Test1 ( ID INT IDENTITY(1,1) NOT NULL, UserName VARCHAR(100) NOT NULL, Age INT )INSERT INTO Test1 SELECT 'A',12 UNION SELECT 'B',13 UNION SELECT 'C',14 UNION SELECT 'D',15DECLARE @TableColumn TABLE(Id INT IDENTITY(1,1),ColumnName VARCHAR(100)) DECLARE @TotalNum INT DECLARE @Line INT DECLARE @Count Int DECLARE @Sqls NVARCHAR(4000) DECLARE @ColumnName VARCHAR(100)SET @Line = 1INSERT INTO @TableColumn SELECT name FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'Test1'SELECT @TotalNum = MAX(ID) FROM @TableColumnWHILE @Line <= @TotalNum BEGIN SELECT @ColumnName = ColumnName FROM @TableColumn WHERE Id = @Line
set @Sqls='select @a=count(1) from Test1 WHERE ' + @ColumnName + ' like ''15%''' exec sp_executesql @Sqls,N'@a int output',@Count output
IF @Count > 0 BEGIN SELECT '数值所在列为:' + @ColumnName END SET @Line = @Line + 1 END
http://www.cnblogs.com/pengyq/archive/2010/02/09/1666626.html
CREATE TABLE Test1
(
ID INT IDENTITY(1,1) NOT NULL,
UserName VARCHAR(100) NOT NULL,
Age INT
)INSERT INTO Test1
SELECT 'A',12 UNION
SELECT 'B',13 UNION
SELECT 'C',14 UNION
SELECT 'D',15DECLARE @TableColumn TABLE(Id INT IDENTITY(1,1),ColumnName VARCHAR(100))
DECLARE @TotalNum INT
DECLARE @Line INT
DECLARE @Count Int
DECLARE @Sqls NVARCHAR(4000)
DECLARE @ColumnName VARCHAR(100)SET @Line = 1INSERT INTO @TableColumn
SELECT name
FROM SYS.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'Test1'SELECT @TotalNum = MAX(ID)
FROM @TableColumnWHILE @Line <= @TotalNum
BEGIN SELECT @ColumnName = ColumnName
FROM @TableColumn
WHERE Id = @Line
set @Sqls='select @a=count(1) from Test1 WHERE ' + @ColumnName + '= ''15'''
exec sp_executesql @Sqls,N'@a int output',@Count output
IF @Count > 0
BEGIN
SELECT '数值所在列为:' + @ColumnName
END SET @Line = @Line + 1
END若要遍历所有的表,只需要从Sysobjects中选出所有表遍历就可以了SELECT * FROM SYSOBJECTS
WHERE type = 'U'
--用游标遍历
declare @sql varchar(800)
set @sql='798DACD1-8160-45FF-B3E4-9F4840578CDB' --要搜索的字符串
declare @sql varchar(8000)
declare tablecursor cursor local for
select sql='if exists ( select 1 from ['+ s.name + '].['+o.name+'] where ['+c.name+'] like ''%'+@sql+'%'' )
print ''所在的表及字段:['+o.name+'].['+c.name+']'''
from sys.syscolumns c
join sys.objects o on c.id=o.object_id
join sys.schemas s on o.schema_id = s.schema_id
where s.name != 'sys' and o.type='u' and c.status>=0 and c.xusertype in (167,175, 239, 231)open tablecursor
fetch next from tablecursor into @sql
while @@fetch_status=0
begin
exec( @sql )
fetch next from tablecursor into @sql
end
close tablecursor
deallocate tablecursor
用like
CREATE TABLE Test1
(
ID INT IDENTITY(1,1) NOT NULL,
UserName VARCHAR(100) NOT NULL,
Age INT
)INSERT INTO Test1
SELECT 'A',12 UNION
SELECT 'B',13 UNION
SELECT 'C',14 UNION
SELECT 'D',15DECLARE @TableColumn TABLE(Id INT IDENTITY(1,1),ColumnName VARCHAR(100))
DECLARE @TotalNum INT
DECLARE @Line INT
DECLARE @Count Int
DECLARE @Sqls NVARCHAR(4000)
DECLARE @ColumnName VARCHAR(100)SET @Line = 1INSERT INTO @TableColumn
SELECT name
FROM SYS.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'Test1'SELECT @TotalNum = MAX(ID)
FROM @TableColumnWHILE @Line <= @TotalNum
BEGIN SELECT @ColumnName = ColumnName
FROM @TableColumn
WHERE Id = @Line
set @Sqls='select @a=count(1) from Test1 WHERE ' + @ColumnName + ' like ''15%''' exec sp_executesql @Sqls,N'@a int output',@Count output
IF @Count > 0
BEGIN
SELECT '数值所在列为:' + @ColumnName
END SET @Line = @Line + 1
END