Ask the Expert

Sysindexes replacement in SQL Server 2005

SQL Server 2000 uses sysindexes to look for the IAM page. What view is used by SQL Server 2005 to perform the same operations?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: