Q

Sysindexes replacement in SQL Server 2005

SQL Server 2005 has a set of internal tables which functions as SQL Server 2000's sysindexes. However, the user does not have access to the tables, but can get a glimpse of what they do.

SQL Server 2000 uses sysindexes to look for the IAM page. What view is used by SQL Server 2005 to perform the same operations?
SQL Server 2005 has its own set of internal tables that users can not access. The set is used to perform operations like this. We can get a glimpse into what these tables do by looking at the definitions for the catalog and compatibility views. For instance, the following code shows the definition for the sys.sysindexes view:

select object_definition(object_id('sys.sysindexes'))

If you look at the output, you'll see that this view gets its value for FirstIAM from a column that results from the following expression:

OpenRowset(TABLE INDEXPROP, id, indid, rowset)

Unfortunately, users have no access to this special syntax -- attempting to use it results in an error.

This was first published in May 2006

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close