Querying the system views of your iMIS db

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