Reliable way to retrieve all UNIQUEIDENTIFIER values from all columns

Question

The goals is to get all UNIQUEIDENTIFIER values from all the columns in the database.

The following dynamic SQL snippet should load all the desired values based on the given variable.

DECLARE @TableNames TABLE
(
    ID INT NOT NULL IDENTITY(0, 1),
    TableName NVARCHAR(50) NOT NULL,
    ColName NVARCHAR(50) NOT NULL
);

DECLARE @Guids TABLE
(
    ID INT NOT NULL IDENTITY(0, 1),
    FoundGuid UNIQUEIDENTIFIER NOT NULL
);

DECLARE @Local NVARCHAR(50);

WHILE @Counter < 500
BEGIN
    SELECT @Local = TableName FROM @TableNames WHERE Id = @Counter;
    INSERT INTO @Guids EXEC('SELECT Id FROM [' + @Local + ']');
    SET @Counter = @Counter + 1;
END;

Is this approach reliable? If not, what would be a better solution?

Answer 1

I would use the system views to generate dynamic sql. This is 100% accurate and not limited to only those columns named Id. It won't matter what schema or column name is used. This will get you all those values with no looping at all.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(c.name)
    + ' FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' UNION ALL '
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.types ty on ty.user_type_id = c.user_type_id
join sys.schemas s on s.schema_id = t.schema_id
where ty.name = 'uniqueidentifier'

--removes the last UNION ALL
select @SQL = left(@SQL, len(@SQL) - 10)

select @SQL

--uncomment below to execute the dynamic sql when you are comfortable it is correct
--exec sp_executesql @SQL