THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Did You Know: How Compatible are the Compatibility Views?

You're probably aware that the metadata interface changed completely in SQL Server 2005, and we no longer have direct access to the system tables. Instead, Microsoft provided us with two sets of views: the catalog views and the compatibility  views.

The catalog views are the preferred interface going forward, as they are fully documented and the naming conventions are much more user friendly. There are also no bitstrings, like the status fields that need to be decoded. Each attribute stored in the view is available in a separate column.

The compatibility views are there to let you use the same code that you used in SQL Server 2000, hopefully temporarily, while you convert your code to the new metadata using the catalog views. The compatibility views have the same names as the system tables in SQL Server 2000, and the column names are the same.

You might think that writing a query against the compatibility views would then allow you to continue to write metadata queries that work with either SQL 2000 or SQL 2005. This might be true to some extent, but we might have to define what we mean by 'work'. Another change that happened in SQL 2005 was the separation of users and schemas.  Users can have permissions granted to them, including permissions to create objects, but the objects exist in containers called schemas.

In SQL 2000, users and schemas are treated interchangeably. If you create a user sue, SQL Server automatically creates a schema sue which is the user sue's default schema. When the user sue selects from an unqualified object t1, SQL Server 2000 assumes she is selecting from an object sue.t1. (If there is no object sue.t1, SQL Server then looks for dbo.t1.)

In SQL 2005, a user sue can have any schema as her default schema and there may or may not be a schema named sue.  When accessing an object that is not in  your default schema or in the dbo schema, you must qualify the object with the schema name, not the owner  name.

So what's my point here?

If you're looking up information about objects in aSQL 2005 database, would you rather know the owner of your objects or the schema they are contained in? I suggest that it is more often the case that you want to know the schema, so that you can then access the objects you are exploring.  And here's where the incompatibility of the compatibility views comes in.

In SQL 2000, the following code will give you the owner and name of all your user tables, and you can then use the information returned to access the objects. I wrote this query this morning in answer to a newsgroup posting asking for information about getting owner and object information from both SQL 2000 and 2005.

SELECT u.name as [user],
       o.NAME as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U';

In SQL 2005, the same code will give you the owner name, but that is NOT the name needed to access the objects. Here's an example that uses a test database, and creates a login sue, a user sue, and a schema called sue_schema. The user sue is given permission to create tables and to alter the sue_schema schema.  The user sue then creates a table called sue_table.

USE testdb;
GO
CREATE LOGIN sue
    WITH PASSWORD = 'sue_password';
CREATE USER sue FOR LOGIN sue;
GO
CREATE SCHEMA sue_schema;
GO
GRANT CREATE TABLE TO sue;
GO
GRANT ALTER ON schema::sue_schema TO sue;
GO
EXECUTE AS user='sue';
GO
CREATE TABLE sue_schema.sue_table (a int);

We can run the metadata query above, and add a filter to look for sue-type objects:

SELECT u.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U' AND o.name like '%sue%';

I get these results:

user    object
------- ---------
sue     sue_table

However, for table access, this information is not helpful. I cannot execute the following statement, even as dbo with full privileges:

REVERT; -- so I no longer execute as user 'sue'
SELECT * FROM sue.sue_table;

So is the metadata query really compatible? It may be compatible in the facts it gives back, but in function, it is not. For SQL Server 2005, the query does not tell us what we need to know in order to access object. Instead, we'll need a catalog view sys.schemas, which has no equivalent in SQL 2000.

SELECT s.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sys.schemas s
           ON o.uid = s.schema_id
  WHERE   type = 'U' AND o.name like '%sue%';

So because of the split between users and schemas, we can't really write one query that gives us information about how to access objects in both SQL 2000 and SQL 2005.

~Kalen

Published Sunday, September 14, 2008 7:51 PM by Kalen Delaney
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

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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