THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search

Adam Machanic

Adam Machanic, Boston-based independent database consultant, writer, and speaker, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.

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!):

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432689

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

Comments

 

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:

Voted.

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

(required) 
(optional)
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based independent database consultant, writer, and speaker. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. 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 "Expert SQL Server 2005 Development" (Apress, 2007) and "Inside SQL Server 2005: Query Tuning and Optimization" (Microsoft Press, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and a member of the INETA North American Speakers Bureau.

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement