How to get a list of all schemas in a Sql Server database

Question

I want to get a list of all the schemas in this Sql Server database. Using the ADO.NET Schema Search API, I get a list of all the collections, but there is no collection for the Schema. I could move the 'Tables' , 'Procedures' collections (and others if necessary) and get a list of unique schema names, but is there a simpler / shorter way to achieve the same result?

Example: for the standard 'AdventureWorks' database, I would also like to get the following list - dbo,HumanResources,Person,Production,Purchasing,Sales (I skipped other standard schema names, for example db_accessadmin , db_datareader , etc.)

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

Answer 1

During 2005 and later, they both give what you are looking for.

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

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

 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 

That "backward incompatibility" is noted in @Adrift's answer.

SQL Server 2000 (and below) does not actually have “schemas,” although you can use roles as namespaces in a similar way. In this case, it may be the closest equivalent.

 SELECT * FROM sysusers WHERE gid <> 0 

Answer 2

Try this query here:

 SELECT * FROM sys.schemas 

This will give you the name and schema_id for all that define the schemas in the database in which you are executing this.

I really don’t know what you mean by the request of the “Schema API” - these are sys. directory representations sys. (in the sys schema) are the best choice for any system information about the databases and objects in these databases.

Answer 3

 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'; 

Answer 4

You can also request the INFORMATION_SCHEMA.SCHEMATA view:

 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA 

I believe that it is recommended to query INFORMATION_SCHEMA views, as they protect you from changes to sys base tables. In SQL Server 2008 R2 Help:

Information schema views provide an internal, system table-independent representation of SQL Server metadata. Permissions for presenting application information schemas work correctly although significant changes have been made to the underlying system tables. The information schema schemas presented in SQL Server comply with the ISO standard definition for INFORMATION_SCHEMA.

Ironically, this note immediately precedes this:

Some changes have been made to the presentation of information schemes that interrupt backward compatibility. These changes are described in the specific views sections.

Answer 5

If you use Sql Server Management Studio, you can get a list of all the schemes, create your own scheme or delete the existing one by looking at:

Databases - [Your database] - Security - Schemes

Answer 6

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