System stored
procedures are the original metadata access tool, in addition to the system tables themselves.
Most of the system
stored procedures introduced in the very first version of SQL Server are still
available. However, catalog views are a big improvement over these procedures:
you have control over how much of the metadata you see because you can query
the views as if they were tables. With the system stored procedures, you
basically have to accept the data that it returns. Some of the procedures allow
parameters, but they are very limited. So for the sp_helpdb procedure, you
can pass a parameter to see just one database’s
information or not pass a parameter and see information for all databases. However,
if you want to see only databases that the login mybasicknowledge owns, or just see
databases that are in a lower
compatibility level, you cannot do it using the supplied stored
procedure. Using the catalog views, these queries are straightforward:
SELECT name FROM sys.databases WHERE
suser_sname(owner_sid) ='mybasicknowledge';
SELECT
name FROM sys.databases WHERE compatibility_level < 90;
