Get SQL Server user table statistics; based on https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database

sql_server_user_table_statistics.sql

select 
    s.name as SchemaName,
    t.name as TableName,
    i.name as indexName,
    p.rows,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from 
    sys.tables t
inner join
    sys.schemas s on t.schema_id = s.schema_id
inner join
    sys.indexes i on t.object_id = i.object_id
inner join
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
where
    t.is_ms_shipped = 0 -- not internal to SQL Server
	and
	(not exists (
        select ep.major_id
        from sys.extended_properties ep
        where 
		    ep.major_id = t.object_id and
            ep.minor_id = 0 and
            ep.class = 1 and
            ep.name = N'microsoft_database_tools_support'
		)
    ) -- not internal to SQL Server Management Studio
	and
    i.index_id <= 1
group by
    s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
    s.name, t.name