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
sys.sysusersis a compatibility view. For current code, joinsys.schemastosys.database_principalsonprincipal_id.- The light version returns only the schema names, not their owner.