How do I obtain a list of all schemas in a Sql Server database

Question

Hi,

I want to retrieve a list of all schemas in a given Sql Server database. Using the ADO.NET schema retrieval API I get a list of all collections but there is no collection for 'Schemas'. I could traverse the 'Tables', 'Procedures' collections (and others if required) and obtain a list of unique schema names but isn't there a easier/shorter way of achieving the same result?

Example: For the standard 'AdventureWorks' database I would like too obtain the following list - dbo,HumanResources,Person,Production,Purchasing,Sales (I've omitted the other standard schem names like db_accessadmin,db_datareader etc)

Edit: I can get the list of schemas by querying the system view - INFORMATION_SCHEMA.SCHEMATA but would prefer using the schema API as first choice.

Answer 1

Try this query here:

SELECT * FROM sys.schemas

This will give you the name and schema_id for all defines schemas in the database you execute this in.

I don't really know what you mean by querying the "schema API" - these sys. catalog views (in the sys schema) are your best bet for any system information about databases and objects in those databases.

Answer 2

You can also query the INFORMATION_SCHEMA.SCHEMATA view:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

I believe querying the INFORMATION_SCHEMA views is recommended as they protect you from changes to the underlying sys tables. From the SQL Server 2008 R2 Help:

Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Ironically, this is immediately preceded by this note:

Some changes have been made to the information schema views that break backward compatibility. These changes are described in the topics for the specific views.

Answer 3

For 2005 and later, these will both give what you're looking for.

SELECT name FROM sys.schemas
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

For 2000, this will give a list of the databases in the instance.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

That's the "backward incompatability" noted in @Adrift's answer.

In SQL Server 2000 (and lower), there aren't really "schemas" as such, although you can use roles as namespaces in a similar way. In that case, this may be the closest equivalent.

SELECT * FROM sysusers WHERE gid <> 0