Script to know number of records ,datapages ,size for each table in Database

Script to know number of record ,datapages ,size for each table in Database

     Whenever a developer is doing r&nd on a project/open source platform which had 1 or more database In associate ,mostly require to know how many rows are there in which table before action and after action. in that developer can use that scipt(sql instruction).

Script >>

SELECT
t.NAME AS TableName,
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) as TotalSpaceKB,
(sum(a.used_pages) * 8) as UsedSpaceKB,
(sum(a.data_pages) * 8) as DataSpaceKB,
(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.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
--WHERE
-- t.NAME NOT LIKE 'dt%'

Group by t.NAME ,p.[Rows]

    
How it can helpful to developer/manager/analyst/review/dba?

•    to know which table is consuming more resource..
•    which table causes the performance of application down.
•    how can we optimize data-pages.
•   & many more depending upon requirement for particular resource/application.

Objects/terms used :-

sys.tables :- Contains a rows of table in a database

sys.indexes :- Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function

sys.partitions :-Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

sys.allocation_unit:-Contains a row for each allocation unit in the database.

Demonstration :- 
     

 
-
Sunit
technocrats@sunitkanyan.in