• Hi, awesome script.

    It does not take a schema name into a picture, and fails for all tables in a non-DBO schemas.

    I had to corret it:

    set nocount on

    if exists(select name from tempdb..sysobjects where name='##tmp') drop table ##tmp

    if exists(select name from tempdb..sysobjects where name='##tmp2') drop table ##tmp2

    --first temp table can hold the grouped data

    --credit to Thava for gathering index counts

    SELECTt.name AS TableName, t.[object_id],

    SUM ( CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END ) AS Primarykey,

    SUM ( CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END ) AS ClusteredIndex,

    SUM ( CASE WHEN i.[type] = 2 THEN 1 ELSE 0 END ) AS NonClusteredIndex,

    SUM ( CASE WHEN i.[type] = 0 THEN 1 ELSE 0 END ) AS HeapIndex,

    COUNT ( * ) TotalNoofIndex into ##tmp2

    FROM sys.tables t

    LEFT OUTER JOIN sys.indexes i

    ON i.[object_id] = t.[object_id]

    GROUP BY

    t.name, t.[object_id]

    order by TableName asc

    --second temp table will hold the sizes

    create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))

    go

    declare @tblname varchar(50)

    declare tblname CURSOR for

    SELECT c.name+'.'+a.name table_name

    FROM sys.objects a WITH (NOLOCK)

    join sys.dm_db_partition_stats b WITH (NOLOCK)

    on a.object_id = b.object_id

    join sys.schemas c WITH (NOLOCK)

    on a.schema_id = c.schema_id

    WHERE a.type='u' AND b.index_id < 2

    GROUP BY a.name , c.name

    open tblname

    Fetch next from tblname into @tblname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    insert into ##tmp

    exec sp_spaceused @tblname

    FETCH NEXT FROM tblname INTO @tblname

    END

    CLOSE tblname

    deallocate tblname

    go

    update ##tmp set

    res = round((cast(REPLACE(res,' KB','') as real) / 1024),-1), --convert to MB

    data = round((cast(REPLACE(data,' KB','') as real) / 1024),-1),

    ind_sze = round((cast(REPLACE(ind_sze,' KB','') as real) / 1024),-1),

    unsed = round((cast(REPLACE(unsed,' KB','') as real) / 1024),-1)

    select nam Table_Name,rows Total_Rows,res Total_Size_MB,data Data_size_MB,ind_sze Index_Size_MB,unsed Unused_Space,##tmp2.TotalNoofIndex as [Number of Indexes],##tmp2.ClusteredIndex as [NumClustered], ##tmp2.NonClusteredIndex as [NumNonClustered], ##tmp2.HeapIndex as [NumHeap], ##tmp2.Primarykey as [Has Primary Key]

    from ##tmp

    join ##tmp2 on ##tmp.nam = ##tmp2.TableName

    order by cast(res as real) desc

    --order by rows desc

    drop table ##tmp

    drop table ##tmp2

    The script uses a fully qualified table nama as <schema>.<table>

    It still is not perfect beacuese it is not showing a fully qualyfied table name, but works in 2005 and 2008

    Alex