Professional Microsoft® SQL Server® 2008 Programming

Appendix B. Going Meta: Getting Data About Your Data

Over the last few releases, Microsoft has done an amazing job of increasing types and volume of data programmatically available about your server and database. It's reached a level where I would be remiss if I didn't give it some kind of coverage.

So, what am I talking about here? Well, SQL Server provides a set of functions — both scalar and tabular — that return targeted information about the current state of your server or database. This can range from simple things like what objects exist on your server (this information was always available, but actual access to it was "unsupported" in many cases) to fragmentation levels for specific indexes.

In this appendix, we're going to provide basic information on a number of things that are called "metadata functions" – also sometimes referred to as dm functions (which stands for database metadata) as well as system views. Much of this tends to be in the domain of the database administrator, but it's important that you have an idea of what information is available for performance tuning as well as for any system state dashboards you may want to include in the administration panels of your application (if you have any). You can also use these for programmatically handling scheduled maintenance tasks (such as only defragmenting indexes that are beyond a certain level of fragmentation or periodic space checks and warnings).

Note that I am going to be largely sticking with the table-valued ...