Show Database and Table Details

use <database_name>;

with cte
    as (select
            t.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 ClusteredIdx,
            sum(case
                  when i.type = 2
                    then 1
                  else 0
                end) as NonClusteredIdx,
            sum(case
                  when i.type = 0
                    then 1
                  else 0
                end) as HeapIdx,
            count(*) as TotalIdxpertable,
            sum(isnull(sta.range_scan_count + sta.singleton_lookup_count, 0)) as 'No of Time Accessed by user untill SERVER started',
			t.create_date as [CreateDate],
			t.modify_date as [ModifyDate]
        from sys.tables as t
             left outer join sys.indexes as i
                 on t.object_id = i.object_id
             left outer join Sys.dm_db_index_operational_stats(db_id(), null, null, null) as sta
                 on
            t.object_id = sta.object_id
        and sta.index_id = i.index_id
        group by
            t.name,
            t.object_id,
			t.create_date,
			t.modify_date)
    select
        TableName,
        Primarykey,
        ClusteredIdx,
        NonClusteredIdx,
        HeapIdx,
        TotalIdxpertable,
        [No of Time Accessed by user untill SERVER started],
		[CreateDate],
		[ModifyDate]
    from cte
    order by
        NonClusteredIdx desc;
go

select
    o.type_desc + 'S' as Details,
    count(*) as Total
from sys.objects as o
group by
    o.type_desc
order by
    o.type_desc;
go