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