现有语句可查出表名,列名,还差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
不是当作参数,是查询结果中加上这个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
------------- 以上为测试表及测试数据
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
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
*/
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
------------- 以上为测试表及测试数据
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
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
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
------------- 以上为测试表及测试数据
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
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
------------- 以上为测试表及测试数据
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
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
不是当作参数,是查询结果中加上这个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
------------- 以上为测试表及测试数据
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
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
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
------------- 以上为测试表及测试数据
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
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
*/
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
------------- 以上为测试表及测试数据
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
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
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
------------- 以上为测试表及测试数据
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
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
------------- 以上为测试表及测试数据
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
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