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