如整个库中 查找值=‘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值?
3 个解决方案
#1
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'''
试试
select '''+@table+''','''+@col+''','+'id from '+@table+' where isYes=''已审核'' and '+@col+'=''123'''
试试
#2
你有qq或邮箱吗,我对于sql开发较弱,有不明白的问题可以请教你。
#3
你可以将所有列都转成行,这是我自己用的脚本
--搜索所有表列数据
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
#1
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'''
试试
select '''+@table+''','''+@col+''','+'id from '+@table+' where isYes=''已审核'' and '+@col+'=''123'''
试试
#2
你有qq或邮箱吗,我对于sql开发较弱,有不明白的问题可以请教你。
#3
你可以将所有列都转成行,这是我自己用的脚本
--搜索所有表列数据
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