Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find all clustered indexes Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 11:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
Hi, I am looking for a script to find out all clustered indexes which are not in primary file group. Please help.
Post #1376783
Posted Wednesday, October 24, 2012 11:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 5,970, Visits: 12,868
Something along the lines of this should get you started

Select * from sys.indexes
Where index_id = 1
And data_space_id > 1



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1376788
Posted Thursday, October 25, 2012 5:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 5,970, Visits: 12,868
This is an improvement

select		OBJECT_NAME(i.object_id) AS TableName
, i.name AS IndexName
, FILEGROUP_NAME(i.data_space_id) AS FileGrpName
from sys.indexes i inner join sys.objects o
on i.object_id = o.object_id
where i.index_id = 1 and o.is_ms_shipped <> 1
and i.data_space_id > 1



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1376940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse