关于sql查询包含某值的数据库内所有表信息

[tj_libo]

现有一需求，需要查询库中所有包含某值，并且某列的值等于指定内容的所有表信息，
如整个库中 查找值=‘123’，并且isYes列的值=已审核的所有表名，123所对应的表名，字段名，所在行ID值
例：表A 有字段
id，name，age，sex，isYes
1 123 12 男 已审核
2 223 12 男 已审核
3 123 12 女 已作废
表B有
id，class，name，like，isYes
1 234 222 唱歌 已审核
2 123 232 跳舞 已审核
3 123 123 跑步 已审核
现查找含有123的，id=1的数据结果为
表名 列名 id值
表A name 1
表B class 2
表B class 3
现有语句可查出表名，列名，还差ID值不知道怎么弄
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table VARCHAR(300),@col VARCHAR(300),@col2 VARCHAR(300)
DECLARE @sql VARCHAR(4000)
CREATE TABLE #t(PK_Name VARCHAR(300),PK_COLUMN VARCHAR(300),PK_ID VARCHAR(300))
DECLARE c CURSOR FOR SELECT name FROM sysobjects a
WHERE a.xtype='U'
AND EXISTS(SELECT 1 FROM syscolumns b WHERE b.id=a.id AND b.name='isYes')
OPEN c
FETCH NEXT FROM c INTO @table
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE col CURSOR FOR SELECT name FROM syscolumns a WHERE OBJECT_NAME(id)=@table AND
EXISTS(SELECT 1 FROM sys.types b WHERE system_type_id=a.xtype AND b.name IN ('char','varchar','nchar','nvarchar') )
OPEN col
FETCH NEXT FROM col INTO @col
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='if exists(select 1 from '+@table+' where isYes=''已审核'' and '+@col+'='‘123’') insert into #t
select '''+@table+''','''+@col+''','''+@col2+''''
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM col INTO @col
END
CLOSE col
DEALLOCATE col
FETCH NEXT FROM c INTO @table
end
CLOSE c
DEALLOCATE c
SELECT * FROM #t

DROP TABLE #t

end


上面语句在哪里添加查找ID值？

[shinger126]

SET @sql='if exists(select 1 from '+@table+' where isYes=''已审核'' and '+@col+'=''123'') insert into #t select '''+@table+''','''+@col+''','+'id from '+@table+' where isYes=''已审核'' and '+@col+'=''123''' 试试

[tj_libo]

引用 1 楼 shinger126 的回复:

SET @sql='if exists(select 1 from '+@table+' where isYes=''已审核'' and '+@col+'=''123'') insert into #t select '''+@table+''','''+@col+''','+'id from '+@table+' where isYes=''已审核'' and '+@col+'=''123''' 试试

你有qq或邮箱吗，我对于sql开发较弱，有不明白的问题可以请教你。

[道素]

你可以将所有列都转成行，这是我自己用的脚本

--搜索所有表列数据
DECLARE @string VARCHAR(100)='11'
DECLARE @sql NVARCHAR(MAX)
SET @sql=N'DECLARE @sql NVARCHAR(MAX),@ColNames NVARCHAR(MAX),@ColValues NVARCHAR(MAX);SET @ColNames=NULL;SET @ColValues=NULL;
SELECT  @ColNames=ISNULL(@ColNames+'','','''')+QUOTENAME(c.[Name])
      ,@ColValues=ISNULL(@ColValues, ''''''''+OBJECT_NAME(c.object_id)+''''''''+'' AS [TableName]'')+'',''+ ''RTRIM(CONVERT(NVARCHAR(max),''+QUOTENAME(c.[Name])+'')) AS ''+QUOTENAME(c.[Name]) 
FROM sys.[columns] AS c where c.object_id=object_id(N''?'');
set @sql= ''SELECT * FROM (SELECT ''+ @ColValues+'' FROM ''+''?''+'' ) as t UNPIVOT(ColumnValue FOR ColumName  IN (''+@ColNames+'')) u WHERE CHARINDEX('''''+@string+N''''',ColumnValue)>0'';
print @sql;
EXEC(@sql)'
PRINT @sql
EXEC sp_MsforeachTable  @command1=@sql