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

现有一需求，需要查询库中所有包含某值，并且某列的值等于指定内容的所有表信息，
如整个库中 查找值=‘123’，并且ID列的值=1的所有表名，123所对应的字段名，
例：表A 有字段
id，name，age，sex 
1     123      12     男
2     223      12     男
3     123      12     女
表B有
id，class，name，like
1    234      222      唱歌
1    123      232      跳舞
2    123     123       跑步
现查找含有123的，id=1的数据结果为
表名   列名   id值
表A   name    1
表B   class     1

现有语句可查出表名，列名，还差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='id')
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 id=''1'' 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值？

15 个解决方案

#1

你是要将id的值参数化传入查询么？

#2

引用 1 楼 shinger126 的回复:

你是要将id的值参数化传入查询么？

不是当作参数，是查询结果中加上这个ID的值 
表名   列名   id值
表A   name    1
表B   class     1

#3

想要的结果就是查出来   在数据库中 包含xxx字符串 的 所有表名，列名和此条数据的ID值。

#4

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	id INT PRIMARY KEY,
	[name] NVARCHAR(20),
	[age] INT,
	sex NCHAR(1)
)
CREATE TABLE B(
	id INT,
	[class] NVARCHAR(20),
	[name] NVARCHAR(20),
	[like] NvarCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO A
SELECT 1,123,12,N'男'
UNION ALL SELECT 2,223,12,N'男'
UNION ALL SELECT 3,123,12,N'女'

INSERT INTO B
SELECT 1,234,222,N'唱歌'
UNION ALL SELECT 1,123,232,N'跳舞'
UNION ALL SELECT 2,123,123,N'跑步'
GO
------------- 以上为测试表及测试数据

--循环用的表变量
DECLARE @tables TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	tableName NVARCHAR(200)	
)
DECLARE @columns TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	columnName NVARCHAR(200)
)
--结果表变量
DECLARE @r TABLE (
	tableName NVARCHAR(200),
	columnName NVARCHAR(200),
	idValue  NVARCHAR(200)	
)
--设定的搜索值
DECLARE @idValue NVARCHAR(50),@colValue NVARCHAR(50)
SET @idValue='1'
SET @colValue='123'

INSERT INTO @tables(tableName)
SELECT t.name FROM sys.tables AS t WHERE EXISTS
(
	SELECT * FROM sys.[columns] AS c WHERE t.[object_id]=c.[object_id] AND c.name='id'	
)

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @tables

DECLARE @j INT,@jmax INT,@sql NVARCHAR(MAX),@tableName NVARCHAR(200)

WHILE @i<=@imax
BEGIN
	SELECT @tableName=tableName FROM @tables WHERE rowNum=@i;

	DELETE FROM @columns

	INSERT INTO @columns(columnName)
	SELECT c.name  FROM sys.[columns] AS c 
	WHERE OBJECT_ID(@tableName)= c.object_id AND c.name!='id'

	IF NOT EXISTS (SELECT 1 FROM @columns)
		CONTINUE;

	SELECT @j=MIN(rowNum),@jmax=MAX(rowNum) FROM @columns
	WHILE @j<=@jmax
	BEGIN
		SELECT @sql='select '''+@tableName +''' AS tableName, '''
			   +c.columnName+''' AS columnName,'+@idValue+' from '+@tableName +' where id='+@idValue+' and ['+c.columnName+']= '''+@colValue+''' '
		  FROM @columns c WHERE rowNum=@j

		INSERT INTO @r(tableName,columnName,idValue)
		EXEC(@sql)
		SET @j=@j+1
	END	 
	SET @i=@i+1;
END

SELECT * FROM @r
/*
tableName	columnName  idValue
A			name		1
B			class		1
*/

#5

引用 4 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	id INT PRIMARY KEY,
	[name] NVARCHAR(20),
	[age] INT,
	sex NCHAR(1)
)
CREATE TABLE B(
	id INT,
	[class] NVARCHAR(20),
	[name] NVARCHAR(20),
	[like] NvarCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO A
SELECT 1,123,12,N'男'
UNION ALL SELECT 2,223,12,N'男'
UNION ALL SELECT 3,123,12,N'女'

INSERT INTO B
SELECT 1,234,222,N'唱歌'
UNION ALL SELECT 1,123,232,N'跳舞'
UNION ALL SELECT 2,123,123,N'跑步'
GO
------------- 以上为测试表及测试数据

--循环用的表变量
DECLARE @tables TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	tableName NVARCHAR(200)	
)
DECLARE @columns TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	columnName NVARCHAR(200)
)
--结果表变量
DECLARE @r TABLE (
	tableName NVARCHAR(200),
	columnName NVARCHAR(200),
	idValue  NVARCHAR(200)	
)
--设定的搜索值
DECLARE @idValue NVARCHAR(50),@colValue NVARCHAR(50)
SET @idValue='1'
SET @colValue='123'

INSERT INTO @tables(tableName)
SELECT t.name FROM sys.tables AS t WHERE EXISTS
(
	SELECT * FROM sys.[columns] AS c WHERE t.[object_id]=c.[object_id] AND c.name='id'	
)

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @tables

DECLARE @j INT,@jmax INT,@sql NVARCHAR(MAX),@tableName NVARCHAR(200)

WHILE @i<=@imax
BEGIN
	SELECT @tableName=tableName FROM @tables WHERE rowNum=@i;

	DELETE FROM @columns

	INSERT INTO @columns(columnName)
	SELECT c.name  FROM sys.[columns] AS c 
	WHERE OBJECT_ID(@tableName)= c.object_id AND c.name!='id'

	IF NOT EXISTS (SELECT 1 FROM @columns)
		CONTINUE;

	SELECT @j=MIN(rowNum),@jmax=MAX(rowNum) FROM @columns
	WHILE @j<=@jmax
	BEGIN
		SELECT @sql='select '''+@tableName +''' AS tableName, '''
			   +c.columnName+''' AS columnName,'+@idValue+' from '+@tableName +' where id='+@idValue+' and ['+c.columnName+']= '''+@colValue+''' '
		  FROM @columns c WHERE rowNum=@j

		INSERT INTO @r(tableName,columnName,idValue)
		EXEC(@sql)
		SET @j=@j+1
	END	 
	SET @i=@i+1;
END

SELECT * FROM @r
/*
tableName	columnName  idValue
A			name		1
B			class		1
*/

ID并非是参数，而是查询结果显示出来

#6

引用 5 楼 tj_libo 的回复:

ID并非是参数，而是查询结果显示出来

你要求搜索 id=1 而且 其它列值 = 123 记录
最终查到的结果， 难道还有 id!=1 的情况？

#7

引用 6 楼 yenange 的回复:

Quote: 引用 5 楼 tj_libo 的回复:

ID并非是参数，而是查询结果显示出来

你要求搜索 id=1 而且 其它列值 = 123 记录
最终查到的结果， 难道还有 id!=1 的情况？

ID=1是举例说明用的，我2个表内还有其他相同名称的字段 为审核字段，真实条件是是否审核 =已审核 ，因为手懒，就用id=1代替了。55555555
数据库中ID是 gid 

#8

我直传一个参数的，就是一个字符串，然后在数据库所有表内查询 包含此字符串的  表名，字段名，所属id值

#9

引用 8 楼 tj_libo 的回复:

我直传一个参数的，就是一个字符串，然后在数据库所有表内查询 包含此字符串的  表名，字段名，所属id值

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	id INT PRIMARY KEY,
	[name] NVARCHAR(20),
	[age] INT,
	sex NCHAR(1)
)
CREATE TABLE B(
	id INT PRIMARY KEY,
	[class] NVARCHAR(20),
	[name] NVARCHAR(20),
	[like] NvarCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO A
SELECT 1,123,12,N'男'
UNION ALL SELECT 2,223,12,N'男'
UNION ALL SELECT 3,123,12,N'女'

INSERT INTO B
SELECT 1,234,222,N'唱歌'
UNION ALL SELECT 2,123,232,N'跳舞'
UNION ALL SELECT 3,123,123,N'跑步'
GO
------------- 以上为测试表及测试数据

--循环用的表变量
DECLARE @tables TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	tableName NVARCHAR(200),
	pkIdName NVARCHAR(200)
)
DECLARE @columns TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	columnName NVARCHAR(200)
)
--结果表变量
DECLARE @r TABLE (
	tableName NVARCHAR(200),
	columnName NVARCHAR(200),
	idValue  NVARCHAR(200)	
)
--设定的搜索值
DECLARE @colValue NVARCHAR(50)
SET @colValue='123'

INSERT INTO @tables(tableName,pkIdName)
SELECT t.name,(
	SELECT ''+c.name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.is_primary_key =1 AND i.[object_id]=ic.[object_id] AND i.[object_id]=t.[object_id]
	INNER JOIN sys.[columns] AS c ON c.[object_id]=ic.[object_id] AND c.column_id=ic.column_id FOR XML PATH('')
) AS pkIdName 
FROM sys.tables AS t WHERE t.name NOT LIKE '#%'

--SELECT * FROM @tables

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @tables

DECLARE @j INT,@jmax INT,@sql NVARCHAR(MAX),@tableName NVARCHAR(200),@pkIdName NVARCHAR(200)

WHILE @i<=@imax
BEGIN
	SELECT @tableName=tableName,@pkIdName=pkIdName FROM @tables WHERE rowNum=@i;

	DELETE FROM @columns

	INSERT INTO @columns(columnName)
	SELECT c.name  FROM sys.[columns] AS c 
	WHERE OBJECT_ID(@tableName)= c.object_id AND c.name!=@pkIdName

	IF NOT EXISTS (SELECT 1 FROM @columns)
	BEGIN
		SET @i=@i+1;
		CONTINUE;
	END


	SELECT @j=MIN(rowNum),@jmax=MAX(rowNum) FROM @columns
	WHILE @j<=@jmax
	BEGIN
		SELECT @sql='select '''+@tableName +''' AS tableName, '''
			   +c.columnName+''' AS columnName,'+@pkIdName+' from '+@tableName +' where ['+c.columnName+']= '''+@colValue+''' '
		  FROM @columns c WHERE rowNum=@j

		INSERT INTO @r(tableName,columnName,idValue)
		EXEC(@sql)
		SET @j=@j+1
	END	 
	SET @i=@i+1;
END

SELECT * FROM @r

改了一个按你要求单字段内容搜索的。
再不行， 你按我的这个改改就好了。
你能写那么多SQL， 我这个应该也能看懂。

#10

(97 行受影响)

(0 行受影响)

(3 行受影响)
消息 241，级别 16，状态 1，第 1 行
从字符串转换日期和/或时间时，转换失败。

#11

引用 1 楼 shinger126 的回复:

你是要将id的值参数化传入查询么？

是将ID的值显示到查询结果中

#12

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	id INT PRIMARY KEY,
	[name] NVARCHAR(20),
	[age] INT,
	sex NCHAR(1)
)
CREATE TABLE B(
	id INT PRIMARY KEY,
	[class] NVARCHAR(20),
	[name] NVARCHAR(20),
	[like] NvarCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO A
SELECT 1,123,12,N'男'
UNION ALL SELECT 2,223,12,N'男'
UNION ALL SELECT 3,123,12,N'女'

INSERT INTO B
SELECT 1,234,222,N'唱歌'
UNION ALL SELECT 2,123,232,N'跳舞'
UNION ALL SELECT 3,123,123,N'跑步'
GO
------------- 以上为测试表及测试数据

--循环用的表变量
DECLARE @tables TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	tableName NVARCHAR(200),
	pkIdName NVARCHAR(200)
)
DECLARE @columns TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	columnName NVARCHAR(200)
)
--结果表变量
DECLARE @r TABLE (
	tableName NVARCHAR(200),
	columnName NVARCHAR(200),
	idValue  NVARCHAR(200)	
)
--设定的搜索值
DECLARE @colValue NVARCHAR(50)
SET @colValue='123'

INSERT INTO @tables(tableName,pkIdName)
SELECT t.name,(
	SELECT ''+c.name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.is_primary_key =1 AND i.[object_id]=ic.[object_id] AND i.[object_id]=t.[object_id]
	INNER JOIN sys.[columns] AS c ON c.[object_id]=ic.[object_id] AND c.column_id=ic.column_id FOR XML PATH('')
) AS pkIdName 
FROM sys.tables AS t WHERE t.name NOT LIKE '#%'

--SELECT * FROM @tables

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @tables

DECLARE @j INT,@jmax INT,@sql NVARCHAR(MAX),@tableName NVARCHAR(200),@pkIdName NVARCHAR(200)

WHILE @i<=@imax
BEGIN
	SELECT @tableName=tableName,@pkIdName=pkIdName FROM @tables WHERE rowNum=@i;

	DELETE FROM @columns

	INSERT INTO @columns(columnName)
	SELECT c.name  FROM sys.[columns] AS c 
	WHERE OBJECT_ID(@tableName)= c.object_id AND c.name!=@pkIdName

	IF NOT EXISTS (SELECT 1 FROM @columns)
	BEGIN
		SET @i=@i+1;
		CONTINUE;
	END


	SELECT @j=MIN(rowNum),@jmax=MAX(rowNum) FROM @columns
	WHILE @j<=@jmax
	BEGIN
		SELECT @sql='select '''+@tableName +''' AS tableName, '''
			   +c.columnName+''' AS columnName,'+@pkIdName+' from '+@tableName +' where cast(['+c.columnName+'] as nvarchar(max)) = '''+@colValue+''' '
		  FROM @columns c WHERE rowNum=@j

		INSERT INTO @r(tableName,columnName,idValue)
		EXEC(@sql)
		SET @j=@j+1
	END	 
	SET @i=@i+1;
END

SELECT * FROM @r

#13

消息 207，级别 16，状态 1，第 1 行
列名 'IdNameCreateTime' 无效。

所有的datetime类型字段都报这个错误

#14

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	id INT PRIMARY KEY,
	[name] NVARCHAR(20),
	[age] INT,
	sex NCHAR(1)
)
CREATE TABLE B(
	id INT PRIMARY KEY,
	[class] NVARCHAR(20),
	[name] NVARCHAR(20),
	[like] NvarCHAR(50),
	IdNameCreateTime DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
SET NOCOUNT ON
INSERT INTO A
SELECT 1,123,12,N'男'
UNION ALL SELECT 2,223,12,N'男'
UNION ALL SELECT 3,123,12,N'女'

INSERT INTO B(id,[class],[name],[like])
SELECT 1,234,222,N'唱歌'
UNION ALL SELECT 2,123,232,N'跳舞'
UNION ALL SELECT 3,123,123,N'跑步'
GO
------------- 以上为测试表及测试数据

--循环用的表变量
DECLARE @tables TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	tableName NVARCHAR(200),
	pkIdName NVARCHAR(200)
)
DECLARE @columns TABLE (
	rowNum INT IDENTITY(1,1) PRIMARY KEY,
	columnName NVARCHAR(200)
)
--结果表变量
DECLARE @r TABLE (
	tableName NVARCHAR(200),
	columnName NVARCHAR(200),
	idValue  NVARCHAR(200)	
)
--设定的搜索值
DECLARE @colValue NVARCHAR(50)
SET @colValue='123'

INSERT INTO @tables(tableName,pkIdName)
SELECT t.name,(
	SELECT ''+c.name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.is_primary_key =1 AND i.[object_id]=ic.[object_id] AND i.[object_id]=t.[object_id]
	INNER JOIN sys.[columns] AS c ON c.[object_id]=ic.[object_id] AND c.column_id=ic.column_id FOR XML PATH('')
) AS pkIdName 
FROM sys.tables AS t WHERE t.name NOT LIKE '#%'

--SELECT * FROM @tables

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @tables

DECLARE @j INT,@jmax INT,@sql NVARCHAR(MAX),@tableName NVARCHAR(200),@pkIdName NVARCHAR(200)

WHILE @i<=@imax
BEGIN
	SELECT @tableName=tableName,@pkIdName=pkIdName FROM @tables WHERE rowNum=@i;

	DELETE FROM @columns

	INSERT INTO @columns(columnName)
	SELECT c.name  FROM sys.[columns] AS c 
	WHERE OBJECT_ID(@tableName)= c.object_id AND c.name!=@pkIdName

	IF NOT EXISTS (SELECT 1 FROM @columns)
	BEGIN
		SET @i=@i+1;
		CONTINUE;
	END


	SELECT @j=MIN(rowNum),@jmax=MAX(rowNum) FROM @columns
	WHILE @j<=@jmax
	BEGIN
		SELECT @sql='select '''+@tableName +''' AS tableName, '''
			   +c.columnName+''' AS columnName,'+@pkIdName+' from '+@tableName 
			   +' where cast(['+c.columnName+'] as nvarchar(max)) = '''+@colValue+''' '
		  FROM @columns c WHERE rowNum=@j

		INSERT INTO @r(tableName,columnName,idValue)
		EXEC(@sql)
		SET @j=@j+1
	END	 
	SET @i=@i+1;
END

SELECT * FROM @r

上面的测试表加了一列 datetime ， 没有错误。
说过了， 你自己也想办法排查吧， 全做伸手党也对自己没提高

#15

引用 1 楼 shinger126 的回复:

你是要将id的值参数化传入查询么？

？？？