SQL Server - Catalog Queries
SQL Server consists of a catalog that is a series of SQL system tables used to define the DBMS. Typically, the DBMS provides high level development tools that preclude one from having to directly query the catalog.
There are times when it is useful to be able to directly query the catalog. On many occasions over the years I have written code generation tools that require direct access to the catalog.
It should also be noted that the SQL Server Catalog is quite robust. In SQL Server 2005 the SQL catalog consists of over 285 tables.
Using the queries presented presumes that you have read access to the SQL Server system tables.