67

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.

alwayslearning
  • 4,253
  • 5
  • 32
  • 43

6 Answers6

80

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
harpo
  • 37,686
  • 13
  • 90
  • 126
  • I tried using this on a sql 2000 server, but I did not get all schemas returned. Anyone know why this happens? – evilfish Sep 20 '12 at 09:32
  • I don't have a 2000 instance around for testing -- and I thought *I* was a real hanger-on! Remember that 2000 doesn't have actual "schemas" like later versions, so what you're really listing is roles. Try just `SELECT * FROM sysusers` and see if you see the identifiers you're looking for. – harpo Sep 20 '12 at 14:55
  • I read up on it and the SELECT * FROM INFORMATION_SCHEMA.SCHEMATA is actually not a good idea to use as far as the sources i read said. A far better method is to execute the master table stored procedure "sp_databases". It works for both 2000 and 2005 (tested both), and in my case returned the correct result every single time. – evilfish Sep 21 '12 at 09:08
  • @evilfish, thanks for the note. Just to be clear, this was a question about listing the *schemas* in a database, where it sounds like you wanted to list the *databases* in an instance. This is confused somewhat by the fact that `INFORMATION_SCHEMA.SCHEMATA` has served both functions in different versions of MS SQL. – harpo Sep 21 '12 at 15:28
13

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.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
  • cool. Your query seems to do the trick too. Wonder whats more efficient - querying sys.schema or INFORMATION_SCHEMA.SCHEMATA view? By 'Schema API' I mean the standard SqlConnection.GetSchema' calls rather than explicitly issuing a query. – alwayslearning Sep 15 '10 at 16:29
  • 2
    The "sys." catalog views are probably a tad more efficient and faster, but they're SQL Server specific. INFORMATION_SCHEMA views are a database-agnostic ANSI standard for querying database metadata, but as they were designed by a standards committee...... you get the idea..... – marc_s Sep 15 '10 at 16:37
  • may be not related , i tried [this](http://stackoverflow.com/a/175450/2218697) with multiple select statements for `PK,FK,D,C,V,UQ` etc to compare source and target database, but then i found [this](http://stackoverflow.com/a/685073/2218697) feature in VS, but is there not a `sql query` to compare complete source and target database ? – Shaiju T Dec 03 '15 at 11:31
6
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';
JayRizzo
  • 2,052
  • 3
  • 25
  • 39
Ashok Tewatia
  • 69
  • 1
  • 3
5

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.

Jeff Ogata
  • 52,923
  • 17
  • 108
  • 124
2

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
gwt
  • 2,171
  • 4
  • 31
  • 56
1

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

[

Matthew
  • 1,182
  • 1
  • 8
  • 16
Marcello
  • 591
  • 6
  • 16