Create globally-scoped metadata views by Adam Machanic

Hi UC,

Thanks for the reply. I didn't say it would be easy to solve, I just said it was a problem :-)

With regard to your three comments, I think #s 1 and 2 are quite straightforward: Handle them as they are handled today, and show the same things that can be shown today if you specify a database name. #3 is obviously the big issue, and you mentioned runtime errors. That's a major problem and there certainly needs to be a way to handle things at least somewhat gracefully. You don't want your entire script to crash and burn just because a database happens to flip into offline mode.

The iteration method you mention looks quite promising. I would be happy to give more feedback as you get more detail on how it might look.

Hi Adam,
Thanks for your request. Providing instance level views for the database specific metadata has lot of issues. Below are some of the key ones:

1. Security - Today, users are stored in the database and logins in master (at instance level). Instance level views will require higher privileges for one.
2. Row-level security - Catalog views use row-level security and allowing instance level access will require more checks / rules.
3. Database state - Database can be auto close state or emergency mode or restore for example. In such cases, opening the database to access the catalog data can result in error or performance problems.

The view behavior also may not be that straight forward. You could get lot of runtime errors depending on the configuration or state of the database.
Having said this, we do see value in providing a mechanism to iterate over data in different databases. One of the options we are considering is the ability to pass a database or table or other references to a query or statement. See below psuedo-example:

-- Returns objects from each database:
select o.*
from sys.databases as d
cross apply (select * from $id(d.name).sys.objects) as o
where d.database_id > 4;

We will see what we can do about improving this scenario in the future.

--
Umachandar, SQL Programmability Team

Absolutely. I have very bad memories of writing a server-wide index management job that had to go and read sysindexes (and later sys.indexes) in each database.

++ It's always surprised me that INFORMATION_SCHEMA.TABLES has a TABLE_CATALOG column, but it will only ever return items in a single database.

finally someone thought about the pain and was smart to add in the connect...I wish MS comes back and says "YES WE CAN"!

Could not agree more. It is a real pain to remember which views have database id and which don't unless I look at the definition of DMV again.

Great suggestion!

And not being able to query sys. tables using a global master SP is a real pain.

Further more there is a real mess as some metadata functions/DMVs allow databases to be specified some don't. This results in really incosistent approaches to writing management scripts.