Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - sys.schemas - Listing All Schemas in a Database

By: FYIcenter.com

(Continued from previous topic...)

How To List All Schemas in a Database?

If you want see all existing schemas in the current database, you can use view sys.schemas as shown in the example below:

USE FyiCenterData;
GO

SELECT * FROM sys.schemas;
GO
name                 schema_id   principal_id
-------------------- ----------- ------------
dbo                  1           1
guest                2           2
INFORMATION_SCHEMA   3           3
sys                  4           4
fyi                  5           1
db_owner             16384       16384
db_accessadmin       16385       16385
db_securityadmin     16386       16386
db_ddladmin          16387       16387
db_backupoperator    16389       16389
db_datareader        16390       16390
db_datawriter        16391       16391
db_denydatareader    16392       16392
db_denydatawriter    16393       16393
(14 row(s) affected)

All schemas, except "fyi", in the list were created by SQL Server.

(Continued on next topic...)

1. What Is a Schema in SQL Server 2005?
2. How To Create a New Schema in a Database?
3. How To List All Schemas in a Database?
4. How To Create a New Table in a Given Schema?
5. How To Transfer an Existing Table from One Schema to Another Schema?
6. How To List All Objects in a Given Schema?
7. What Is the Default Schema of Your Login Session?
8. Who Is the Owner of a Schema?
9. How To Change the Ownership of a Schema?
10. What Happens If You Are Trying to Access a Schema Not Owned by You?
11. How To Drop an Existing Schema?