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.
Dig deeper on Microsoft SQL Server 2005
Related Q&A from Adam Machanic
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert...continue reading
Database Snapshots feature in SQL Server 2005 Enterprise Edition creates read-only files. For these separate databases, our site expert Adam Machanic...continue reading
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.