My favorite teacher for all things SQL really stressed the importance of knowing the system tables that drive SQL Server. That was back in the era of SQL Server 6.0 when Enterprise Manager could be kinda stupid and sometimes fail to reflect the reality of the database. Now, many years later, in SQL Server 2005, the GUI is so much more reliable that it accurately reflects the underlying metadata.
But there are still times where it's very useful to be able to query the metadata. Beginning with SQL 2005, it is no longer recommended that you query the system tables directly. Instead, Microsoft recommends that you use the catalog views:
We recommend using catalog views to access metadata for the following reasons:
All metadata is made available as catalog views.
Catalog views present metadata in a format that is independent of any
catalog table implementation, therefore catalog views are not affected
by changes in the underlying catalog tables.
Catalog views are the most efficient way to access core server metadata.
-
Catalog views are the general interface to catalog metadata and provide
the most direct way to obtain, transform, and present customized forms
of this metadata.
- Catalog view names and the names of their columns are descriptive.
Query results match what might be expected by a user who has a moderate
knowledge of the feature that corresponds to the metadata that is being
queried.
For backwards compatibility, Microsoft included views that mirror the old system tables I am used to and since they're there, I've struggled to change my old habits of relying upon them.
Here are some examples of using the system tables to do some iMIS-related tasks.
Finding all of the triggers in a db
Here's a common one you need for doing sysadmin of an iMIS db. Prior to an upgrade, you will want to make sure you know what triggers you have. The correct way using the catalog view:
select * from sys.triggers
order by name
What permissions exist on a given table?
This query tells you what permissions have been granted on the Name table.
select p.STATE_DESC, p.permission_name, t.name, pr.name,
pr.type_desc
from sys.database_permissions p
inner join sys.database_principals pr
on p.grantee_principal_id = pr.principal_id
inner join sys.tables t on p.major_id = t.object_id
where t.name = 'Name'
Returns a result set like this:
DELETE Name IMIS DATABASE_ROLE
INSERT Name IMIS DATABASE_ROLE
REFERENCES Name IMIS DATABASE_ROLE
SELECT Name IMIS DATABASE_ROLE
UPDATE Name IMIS DATABASE_ROLE
SELECT Name StandardReporting DATABASE_ROLE
