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


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);


  • database_name - database name
  • schema_name - schema name


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.