SQL Server 2005

Analizza SQL Filegroups Analysis

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