Skip to main content
Skip table of contents

List all Schemas of a SQL Instance

SQL
DECLARE @ListSchemas NVARCHAR(MAX)

SELECT @ListSchemas = 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         
              where left(cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT,3)<>''db_'' 
		      AND  (cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT)<>''sys''
		      AND  (cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT)<>''INFORMATION_SCHEMA''
		      AND  (cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT)<>''guest''
'

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

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

EXECUTE (@ListSchemas)
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.