How do I obtain a list of all schemas in a Sql Server database
For 2005 and later, these will both give what you're looking for.
SELECT name FROM sys.schemasSELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATAFor 2000, this will give a list of the databases in the instance.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATAThat'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
Try this query here:
SELECT * FROM sys.schemasThis 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.
SELECT s.name + '.' + ao.name , s.nameFROM sys.all_objects aoINNER JOIN sys.schemas s ON s.schema_id = ao.schema_idWHERE ao.type='u';