您可以推迟执行,直到光标循环完成。然后,只需跟踪循环内的表名称,如果相同,则添加一个 OR,否则结束 SELECT 并开始新的 SELECT。
DECLARE @lasttable varchar(255);
SET @lasttable='';
FETCH NEXT FROM abc INTO @tableName, @columnName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF(@lasttable=@tablename) BEGIN
SET @szQuery = @szQuery + ' OR [' + @columnName + ']=''' + CAST(@searchValue AS varchar(50)) + '''';
END ELSE BEGIN
SET @lasttable = @tablename;
SET @szQuery = @szQuery +
'SELECT '''+@tableName+''' AS TheTable, '''+@columnName+''' AS TheColumn '+
'FROM '+@tableName+' '+
'WHERE '+@columnName+' = '''+CAST(@searchValue AS varchar(50))+''''
END
FETCH NEXT FROM abc INTO @tableName, @columnName;
END
PRINT @szQuery;
EXEC (@szQuery);
您还可以创建存储过程来构建一个 VIEW,该 VIEW 对所有表和 uniqueidentifier 字段执行 UNION ALL。具有这样的架构的东西:
CREATE VIEW all_uuids AS (
SELECT 'prices' AS tablename, 'BookGUID' as fieldname, ID as primarykey, BookGUID AS guid FROM prices
UNION ALL SELECT 'prices', 'AuthorGUID', ID, AuthorGUID FROM prices
UNION ALL SELECT 'othertable', 'otherfield', ID, otherfield FROM othertable
)
然后,您只需在此可重用的 VIEW 上执行单个 SELECT 语句即可获取所有匹配的 GUID。要在单个表中搜索,请使用相关子查询,例如:
SELECT * FROM prices WHERE EXISTS (SELECT null FROM all_uuids u WHERE u.primarykey=prices.id AND u.guid=@searchfor AND u.tablename='prices')
这将搜索价格表中的所有 GUID 字段。 SQL Server 足够智能,不会查找其他表,而是使用现有表的索引。
通过重用单个视图,您只需在更改架构时循环访问 information_schema,而不是每次查询,并且视图的结果比存储过程的结果更容易连接。
Answer
原始海报最终解决方案基于此答案:
CREATE PROCEDURE dbo.FindGUID @searchValue uniqueidentifier AS
/*
Search all tables in the database for a guid
Revision History
6/9/2009: Initally created
6/10/2009: Build or clause of multiple columns on one table
*/
--DECLARE @searchValue uniqueidentifier
--SET @searchValue = '{2A6814B9-8261-452D-A144-13264433864E}'
DECLARE abc CURSOR FOR
SELECT
c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'
WHERE DATA_TYPE = 'uniqueidentifier'
DECLARE @tableSchema varchar(200)
DECLARE @tableName varchar(200)
DECLARE @columnName varchar(200)
DECLARE @szQuery varchar(8000)
SET @szQuery = ''
DECLARE @lasttable varchar(255);
SET @lasttable='';
OPEN ABC
FETCH NEXT FROM abc INTO @tableSchema, @tableName, @columnName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF(@lasttable=@tablename)
BEGIN
SET @szQuery = @szQuery + ' OR [' + @columnName + ']=''' + CAST(@searchValue AS varchar(50)) + '''';
END
ELSE
BEGIN
SET @lasttable = @tablename;
IF @szQuery <> ''
BEGIN
PRINT @szQuery
EXEC ('IF EXISTS (' + @szQuery + ') BEGIN ' + @szQuery + ' END');
END
SET @szQuery =
'SELECT '''+@tableSchema+'.'+@tableName+''' AS TheTable, '''+@columnName+''' AS TheColumn '+
'FROM '+@tableName+' '+
'WHERE '+@columnName+' = '''+CAST(@searchValue AS varchar(50))+''''
END
FETCH NEXT FROM abc INTO @tableSchema, @tableName, @columnName;
END
CLOSE abc
DEALLOCATE abc
IF @szQuery <> ''
BEGIN
PRINT @szQuery
EXEC ('IF EXISTS (' + @szQuery + ') BEGIN ' + @szQuery + ' END');
END
GO