Tuesday, 14 July 2015

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+
    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
    [Source] DESC    -- Sort 'user' schemas to the top

No comments:

Post a Comment