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)