Menu
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.
691
You can get a list of the schemas using an SSMS or T-SQL query. To do this in SSMS, you would connect to the SQL instance, expand the SQL database and view the schemas under the security folder. Alternatively, you could use the sys. schemas to get a list of database schemas and their respective owners.
SQL> select distinct owner from dba_objects; >> Will give you the list of schemas available. select username from dba_users; this output will list all users including sysdba,system and others.
Right-click the Security folder, point to New, and select Schema. In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box. In the Schema owner box, enter the name of a database user or role to own the schema.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
