Saturday, January 31, 2015

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 >>

t.NAME AS TableName,
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
sys.tables t
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID
sys.allocation_units a ON p.partition_id = a.container_id
-- 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 :-