Q

System tables in SQL Server 2005

In reference to your article, Say goodbye to system tables in SQL Server 2005, do you mean system tables are really gone or no longer actively supported? If it is really gone then I have more up front work to do. If it is no longer actively supported I might have more time for changes.
In SQL Server 2005, the system tables from SQL Server 2000 are no longer available in the form of tables. But there are still objects available that look like them. For instance, you might be using the following query in SQL Server 2000 to enumerate all of the indexes for a table called "YourTable":
SELECT name
FROM sysindexes
WHERE id = OBJECT_ID('YourTable')
 AND indid < 0
 AND root < 0x0

In SQL Server 2005, this query will still work. However, the sysindexes table no longer exists as a table; it's

now a view over hidden resource tables that are not directly accessible to users. You can even see the definition of the view, using the following T-SQL:

SELECT OBJECT_DEFINITION(OBJECT_ID('sysindexes'))

You'll notice that most of the data is being selected from a table called sys.sysidxstats, which is unavailable to users. Attempting to query it will result in an invalid object name error.

Since they are no longer tables, and since they are left behind for backwards compatibility, the system tables from SQL Server 2000 are now referred to as compatibility views. These views will, by and large, behave the same from a query standpoint as the system tables in previous versions of SQL Server, but it is recommended that you convert your code to use the new catalog views as soon as possible.

The biggest impact these changes will have in the short term is for people who were overriding SQL Server's security and directly modifying the data in the system tables. This will no longer be possible with the compatibility views, and will break some of that code. But those who are doing this should realize that writing directly to the system tables was never documented or supported by Microsoft – so this was never a good idea. If you are doing this, you should change the code now; not only will this make upgrade to SQL Server 2005 easier, but it will also help ensure that you don't damage your SQL Server 2000 installations!


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in May 2005

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close