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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  0

User · Answer

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

User · Answer

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

User · Answer

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

[c#] How do I obtain a list of all schemas in a Sql Server database

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to ado.net