DMV & DMF

There are two types of dynamic management views and functions:

* Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
* Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission

Dynamic management views and functions have been organized into the following categories.

1. Common Language Runtime Related Dynamic Management Views

2. I/O Related Dynamic Management Views and Functions

3. Database Mirroring Related Dynamic Management Views

4. Query Notifications Related Dynamic Management Views

5. Database Related Dynamic Management Views

6. Replication Related Dynamic Management Views

7. Execution Related Dynamic Management Views and Functions

8. Service Broker Related Dynamic Management Views

9. Full-Text Search Related Dynamic Management Views

10. SQL Server Operating System Related Dynamic Management Views

11. Index Related Dynamic Management Views and Functions

12. Transaction Related Dynamic Management Views and Functions

Excerpt from http://msdn.microsoft.com/en-us/library/ms188754.aspx

DMV Scripts are on Scripts Repository: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true