SQL Query to list ALL scoped credentials in ALL the databases on a server

Question

So I was able to get a list of all the tables/schemas for each db in the server by using this code:

declare @sql nvarchar(max);

select @sql = (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + '  AS database_name,
               s.name COLLATE DATABASE_DEFAULT  AS schema_name,
               t.name COLLATE DATABASE_DEFAULT  AS table_name,
               t.is_external                    AS Table_Type
               FROM '+ quotename(name) + '.sys.tables t
               JOIN '+ quotename(name) + '.sys.schemas s
                    on s.schema_id = t.schema_id'
    from sys.databases 
    where state=0
    order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sql = stuff(@sql, 1, 12, '') + ' order by database_name, 
                                               schema_name,
                                               table_name'
CREATE TABLE #TEMP 
(
    database_name nvarchar(max),
    schema_name nvarchar(max),
    table_name nvarchar(max),
    Table_Type int
)

INSERT INTO #TEMP
    EXEC (@sql);

SELECT *
FROM #TEMP

DROP TABLE #TEMP

But what I would like to do next is the same kind of return but with a list of all the scoped credentials from [database name].sys.database_scoped_credentials and thus far I cannot get the code to run trying to switch it from sys.tables / sys.schema to the sys.database_scoped_credentials only receiving the error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ']'

The edit I have made to try and make this work is:

declare @sqlcred nvarchar(max);

SELECT @sqlcred =
                (select 'SELECT ' +  + quotename(name) + '                          AS database_name,
                                c.name COLLATE DATABASE_DEFAULT                         AS credential_name,
                                c.credential_identity COLLATE DATABASE_DEFAULT          AS credential_identity
                         FROM '+ quotename(name) + '.sys.database_scoped_credentials c'
                 from sys.databases
                 where state=0
                 order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sqlcred = stuff(@sqlcred, 1, 12, '') + ' order by database_name, 
                                               credential_name,
                                               credential_identity'

CREATE TABLE #CRED 
(
    database_name nvarchar(max),
    credential_name nvarchar(max),
    credential_identity nvarchar(max)
)

INSERT INTO #CRED
    EXEC (@sqlcred);

SELECT *
FROM #CRED

DROP TABLE #CRED

Answer 1

Given you have SQL Server 2019, you can use STRING_AGG, which is much easier to write correctly.

You also don't need to use a temp table. You can join and select directly inside the dynamic SQL.

declare @sql nvarchar(max);

select @sql = string_agg(CAST(N'
SELECT
  N' +  + quotename(d.name, '''') + ' AS database_name,
  s.name COLLATE DATABASE_DEFAULT  AS schema_name,
  t.name COLLATE DATABASE_DEFAULT  AS table_name,
  t.is_external                    AS Table_Type
FROM ' + quotename(d.name) + '.sys.tables t
JOIN ' + quotename(d.name) + '.sys.schemas s ON s.schema_id = t.schema_id
' AS nvarchar(max)), '
UNION ALL
'  ) within group (order by d.name)
from sys.databases d
where d.state = 0;

set @sql += '
order by
  database_name, 
  schema_name,
  table_name;
';

print @sql;   -- your friend

exec sp_executesql @sql;

db<>fiddle

Answer 2

The answer ultimately ended up being the following:

declare @sqlcred nvarchar(max);

SELECT @sqlcred =
                (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + '                          AS database_name,
                                c.name COLLATE DATABASE_DEFAULT                         AS credential_name,
                                c.credential_identity COLLATE DATABASE_DEFAULT          AS credential_identity
                         FROM '+ quotename(name) + '.sys.database_scoped_credentials c'
                 from sys.databases
                 where state=0
                 order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sqlcred = stuff(@sqlcred, 1, 20, '') + ' order by database_name, 
                                               credential_name,
                                               credential_identity'
CREATE TABLE #CRED (database_name nvarchar(max),credential_name nvarchar(max),credential_identity nvarchar(max))
INSERT INTO #CRED
EXEC (@sqlcred);

SELECT *
FROM #CRED
DROP TABLE #CRED