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

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 to 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.

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

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.

SELECT s.name + '.' + ao.name
       , s.name
FROM sys.all_objects ao
INNER JOIN sys.schemas s ON s.schema_id = ao.schema_id
WHERE ao.type='u';

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.

If you are using Sql Server Management Studio, you can obtain a list of all schemas, create your own schema or remove an existing one by browsing to:

Databases - [Your Database] - Security - Schemas

[

You can also use the following query to get Schemas for a specific Database user:

select s.schema_id, s.name as schema_name
from sys.schemas s
inner join sys.sysusers u on u.uid = s.principal_id
where u.name='DataBaseUserUserName'
order by s.name