How to know if primary key is CLUSTERED or NONCLUSTERED 1

[popseven]

Hi Guys,

Is there a way to know via query if a PRIMARY KEY on a particular mssql 2000 table was declared as NONCLUSTERED or CLUSTERED index?

TIA,
popseven

 

[gmmastros]

This may not be exactly what you are looking for, but....

sp_helpindex [!]TableName[/!]

This is what I get (for Index Description) for one of my tables...

[tt][blue]
index_description
-------------------------------------------------
clustered, unique, primary key located on PRIMARY
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[popseven]

Hi,

actually, I need to change the index from NONCLUSTERED to CLUSTERED for a particular table but I need to check first (via script) before I change. If it's already CLUSTERED, I will skip it.

 

[gmmastros]

Take a look at this thread. thread183-1151951

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

This query will show you the primary keys that are NOT clustered.

Select  object_name(id) As TableName, 
        Name
From    sysindexes
Where   Status & 16 = [!]0[/!]
        And Status & 2048 = 2048

And the primary keys that are clustered...

Select  object_name(id) As TableName, 
        Name
From    sysindexes
Where   Status & 16 = [!]16[/!]
        And Status & 2048 = 2048

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

You can also use OBJECTPROPERTY with CnstIsClustKey on the constraint name

USE Pubs
Go

SELECT OBJECTPROPERTY(OBJECT_ID('UPKCL_auidind'), 'CnstIsClustKey')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/