I have a CTE in which a SELECT statement is generated, but SQL Server (2012) does not allow calling EXEC on it. Here's the query:
DECLARE @guidToFind uniqueidentifier = 'E4069560-091A-4026-B519-104F1C7693B3';
WITH GuidCols (TableName, ColName, Query) As
(
SELECT
C.TABLE_NAME,
C.COLUMN_NAME,
'SELECT ' +
QUOTENAME(C.TABLE_NAME) + '.' +
QUOTENAME(C.COLUMN_NAME) + '
FROM ' +
QUOTENAME(C.TABLE_NAME) + '
WHERE ' +
QUOTENAME(C.COLUMN_NAME) + ' = ''' + cast(@guidToFind AS VARCHAR(50))+
''''
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
C.DATA_TYPE = 'uniqueidentifier'
)
-- SELECT * FROM
EXEC( GuidCols.Query )
The problem is not solve if I uncomment the SELECT statement after CTE.
The purpose of this query is to find all instances of a GUID in a database. Currently I'm solving this problem using a script like this. However I would be happier to solve this problem without iterating through rows, using set operations and other techniques.