Identify Index Details in SQL Server

The below Query will give all index details in a database.

Index Details :

Declare @IndexDetails as table (IndexName  Varchar(100) ,index_description varchar(max),index_keys varchar(400))

insert into @IndexDetails

exec sp_msforeachtable 'sp_helpindex  ''?'''

select * from @IndexDetails

Index Size and Table Size Details

The below query provides the size of each table ,Number of rows  and index size details.

declare @spacedetails as table(name varchar(100),totalnofofrows int,reservedspace varchar(100),allocatedspace varchar(100),indexsize varchar(100),unusedspace varchar(100))

insert into @spacedetails

exec sp_msforeachtable 'sp_spaceused  ''?'''

select * from @spacedetails

order by totalnofofrows desc

Alternatively we can use query the system tables also

select distinct st.name,rows,sum(total_pages*8)/1024  as size from sys.tables st

join sys.partitions sp on st.object_id = sp.object_id

join sys.allocation_units au on au.container_id = sp.partition_id

where rows > 0

group by st.name,rows

order by size desc