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

c# .net sql-server ado.net
alwayslearning picture alwayslearning · Sep 15, 2010 · Viewed 117.9k times · Source

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

harpo picture harpo · Sep 15, 2010

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

Related questions

ExecuteReader requires an open and available Connection. The connection's current state is Connecting

When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously: ExecuteReader requires an open and available Connection. The connection's current state is Connecting. The site works fine …

Read More
How can I retrieve a table from stored procedure to a datatable?

I created a stored procedure so as to return me a table. Something like this: create procedure sp_returnTable body of procedure select * from table end When I call this stored procedure on the frontend what code do I need …

Read More
The SqlParameter is already contained by another SqlParameterCollection - Does using() {} cheat?

While using the using() {} (sic) blocks as shown below, and assuming that cmd1 does not live beyond the scope of the first using() {} block, why should the second block throw an exception with the message The SqlParameter is already contained …

Read More