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

user_233697 0
Viewed 138306

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.

ado.net sql-server .net c#
6 Answers

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_11625390
Related