THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Connect Item: Globally-Scoped Metadata Views

Ever tried writing a more or less database-agnostic administrative script?

If the experience didn't make your head spin, you either have a stronger constitution than most DBAs or your script doesn't actually work properly. The fact is, due to the way most of the metadata views are currently scoped in SQL Server, pulling data from multiple databases requires a rather annoying set of techniques, usually involving temporary tables, dynamic SQL, and cursors. For a real-world example, just take a look at my Who is Active? script.

This could all be so much easier if the product simply exposed global views, available from anywhere, and keyed with a database_id column. The views could be internally partitioned to ensure great performance if you only need data from a single database, yet still flexible enough to provide data across the entire server. I think this feature would make the overall administrative experience much nicer, so I've created a Connect item, and I hope that you will vote for it (5, of course!):

Thanks for your support!

Published Thursday, April 16, 2009 12:09 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alexander Kuznetsov said:

Great suggestion!

April 16, 2009 11:21 AM

Michelle Ufford said:

Voted, 5 stars!

April 16, 2009 12:40 PM

Ranga Narasimhan said:

Great Idea. voted 5 stars.

April 16, 2009 2:11 PM

Saggi Neumann said:


It reminded me of another annoyance - index_id, used with and returned from sys.dm_db_index_physical_stats. Why aren't there index_name()/index_id() functions that can also take a database_id parameter, with ALTER INDEX the "right way" to rebuild/reorganize indexes?

April 18, 2009 10:21 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement