Technical Article

List schemas in a database

,

Run the script to see which schemas are available in a database.

[SchemaName] = the schema's name

[SchemaOwner] = authorization specified at creation

[Source] is either User, System, or Fixed Role

-- Works on MS SQL Server 2005+
SELECT
a.name As [SchemaName]-- The schema's name
, b.name As [SchemaOwner]-- Set at creation
, CASE 
WHEN a.schema_id <5 THEN 'SYSTEM' 
WHEN a.schema_id >16000 THEN 'FIXED ROLE'
ELSE 'User' 
END As [Source]-- User or system?
FROM sys.schemas a
INNER JOIN sys.schemas b
ON a.principal_id = b.schema_id
ORDER BY 
[Source] DESC-- Sort 'user' schemas to the top

Rate

3 (2)

Share

Share

Rate

3 (2)