How to EXEC a query generated in a CTE

Question

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.

Answer 1

You can not use exec as a part of a CTE.

From WITH common_table_expression (Transact-SQL)

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.

What you can do instead is to store the result from your query in a temp table, loop over the rows and execute one statement at a time.