G’day,

The recommended way to access SQL SERVER metadata is through the catalog views.

The challenge is to be able to distinguish between the catalog views and the compatibility views, because we always want to use the catalog views rather that the compatibility views.

The compatibility views basically provide some degree of backwards compatibility with SQL SERVER 2000 system tables (basically the compatibility views have similar names to the SQL SERVER 2000 system tables but are not exactly the same in all cases), and you’re not guarenteed to get back exactly the same information as you may have expected from going directly to the system tables in SQL SERVER 2000. So the advice is to stick to the catalog views going forward.

So, what do we have to do in order to differentiate between the two, as they both appear in the intellisense of SSMS when we begin typing a name. Well, we could go to look in Books Online. But there is a method that we can use to distinguish between the two without leaving SSMS, and be reasonably sure that the view is either a catalog view or a compatibility view.

You see, both the compatibility views and the catalog views live in the sys schema, but the compatibility views can also be accessed via the dbo schema (for backward compatibility – obviously )

So, if you can query the view from both the sys and dbo schemas, then you lightly have a compatibility view (and you’ll lightly want to steer clear of it), but if you can only query the view from the sys schema then you lightly have a catalog view – this will be the view to use.

Lets look at an example.

Suppose, we want to get a list of databases (for instance to check if a database exists before we drop it).

We could use either of the two queries

SELECT * FROM sys.sysdatabases;

or

SELECT * FROM sys.databases;

but without checking BOL, how do we know which one is the catalog view and which one is the compatibility view?

The answer is, the one that only resides in the sys schema will be the catalog view while which ever one lives in the dbo schema will be the compatibility view,   so lets check it,

we can legally write

SELECT * FROM dbo.sysdatabases;

or even

SELECT * FROM sysdatabases;

as the dbo schema will always be checked, even if it is not the users default schema.Both of the above queries work, so it follows that sysdatabases is a lightly a compatibility view.

Let’s just verify that sys.databases is in fact the lightly catalog view.

If we try to query

SELECT * FROM dbo.databases;

or even

SELECT * FROM databases;

we’ll get an error telling us that we have an invalid object.

Msg 208, Level 16, State 1, Line 1

Invalid object name ‘databases’.

That’s a good indication that sys.databases is in fact the catalog view and the one we should be using.

Conclusion.

The compatibility views live in both the dbo and sys schemas. With a quick test to see if the view lives only in the sys schema, we can be reasonably sure that we have a catalog view – obviously you can check BOL to be 100% certain. I find that once I’ve done this check a few times that I’ll remember it a lot easier in the future than I would have if I simply just checked the documentation each time – but that’s just me

Also, as a general rule of thumb, the catalog views will contain much more metadata (column wise ) than the compatibility views. The catalog views will also contain metadata about the features added to the database in SQL 2005 and later.

Have a good day. Cheers Martin.