Wednesday, March 3, 2010

Queries to get filegroup of table/indexes

--2005
select case when indexes.type_desc in ('HEAP','CLUSTERED')
then 'Table-' + indexes.type_desc
else ' NC Index' end as indexType,
rtrim(cast(
case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(schemas.name + '.'
+ indexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
schemas.name + '.' + objects.name as tableName,
coalesce(indexes.name,'') as indexName,
filegroups.name as filegroup
from sys.indexes as indexes
join sys.objects
on indexes.object_id = objects.object_id
join sys.schemas
on objects.schema_id = schemas.schema_id
join sys.filegroups as filegroups
on indexes.data_space_id = filegroups.data_space_id
where objectproperty(indexes.object_id,'IsMSShipped') = 0
order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end

--2000 (Assumes that owner is DBO)
select case when indid = 0 then 'Table-Heap'
when indid = 1 then 'Table-Clustered'
else ' NC Index'
end as objectType,
rtrim(cast(
case when indexProperty(sysindexes.id,sysindexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(sysindexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
object_name(sysindexes.id) as tableName,
coalesce(sysindexes.name,'') as indexName,
sysfilegroups.groupname as filegroup
from sysindexes
join sysfilegroups
on sysindexes.groupId = sysfilegroups.groupId
where objectproperty(sysindexes.id,'IsMSShipped') = 0
order by object_name(sysindexes.id),indid

1 comment:

  1. I have heard about another way of acces repaire. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

    ReplyDelete