LLI Wiki — My Digital Knowledge Base

SQL Server: list schemas and their owners in all databases

List the schemas of all databases with their owner, and the objects of a schema.

List the schemas of all the databases of an instance with their owner, in a light or a full version, and list the objects of a schema.

Light version: schemas of all databases

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT ' UNION ALL
SELECT ' +  + QUOTENAME(name,'''') + ' as DbName, cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT 
AS Schema_Name FROM ' + QUOTENAME(name) + '.sys.schemas'

FROM sys.databases Order BY [name] FOR XML PATH(''),type).value('.','nvarchar(max)'),1,12,'')

SET @SQL = @SQL + ' ORDER BY DbName, Schema_Name'

EXECUTE (@SQL)

Full version: schemas and owner

Only the schemas whose owner is a database user (not sys, guest or INFORMATION_SCHEMA) are listed, for the user databases.

declare @query nvarchar(max)

set @query = 
(select 'select ''' + name + ''' as database_name,
              s.Name COLLATE DATABASE_DEFAULT as schema_name,
              u.name COLLATE DATABASE_DEFAULT as schema_owner 
        FROM ['+ name + '].sys.schemas s
        JOIN ['+ name + '].sys.sysusers u on u.uid = s.principal_id
        where u.issqluser = 1
              and u.name not in (''sys'', ''guest'', ''INFORMATION_SCHEMA'')
        union all '
    from sys.databases 
    where database_id > 4
    for xml path(''), type).value('.', 'nvarchar(max)')

set @query = left(@query,len(@query)-10) 
                        + ' order by database_name, schema_name'

execute (@query)

List the objects of a schema

This query reads the current database only.

SELECT *
FROM sys.objects WHERE schema_id = SCHEMA_ID('dbo')

Notes

Mastodon LLIMastodon SocialFosstodon