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