List schemas in all databases in SQL Server instance

Query below lists all schemas from all databases on SQL Server instance.

Here you can get list of databases only: link

Query

declare @query nvarchar(max);

set @query =
(select 'select ''' + name + ''' as database_name,
                name COLLATE DATABASE_DEFAULT as schema_name 
         from ['+ name + '].sys.schemas 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);

Columns

  • database_name - database name
  • schema_name - schema name

Rows

  • One row represents one schema within database
  • Scope of rows: all schemas from all databases on SQL Server instance
  • Ordered by database name, schema name

Sample results

0
There are no comments. Click here to write the first comment.