Calculate Table Size and row count

• hybg1107

  Grasshopper

  Points: 21

  More actions

  January 30, 2014 at 9:12 pm

  #287870

  Comments posted to this topic are about the item Calculate Table Size and row count

• Jorge Serres

  Old Hand

  Points: 352

  More actions

  January 31, 2014 at 11:02 am

  #1685150

  Improved version!! I added Schema, since we can have more than 1 table with the same name in the DB.

  Note: We don't need the temporary table as a placeholder.

  In any case if needed, DO NOT use the "SELECT * into #temp_table1"; For bigger DATASETS, this cmd is BAD; It'll create a lockout in Tempdb.

  The best way is to create the place holder via DDL (ie. SELECT * into #temp_table1 where 1 = 2) Once the placeholder is created as #temp_table1

  Then issue the insert stmnt :

  INSERT #temp_table1

  SELECT * FROM WhatEverquery

  This will save you lots of headaches

  here's the improved version which can also b Parameterized as a Sproc if needed

  SELECT s.name +'.' + B.name, CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024)) as [Table Used Size(MB)],

  CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) as [Table Located Size(MB)],

  (CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) - CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024))) as [Table Free Size(MB)] ,

  A.row_count

  from sys.dm_db_partition_stats A join sys.all_objects B

  ON A.object_id = B.object_id

  join sys.schemas S

  on B.schema_id = S.schema_id

  where B.type != 'S'

  group by s.name +'.' + B.name, row_count

  order by s.name +'.' + B.name asc

• Thordog

  Ten Centuries

  Points: 1322

  More actions

  January 31, 2014 at 4:13 pm

  #1685248

  These don't work with partitioned tables or indexes. It lists everything on separate rows.

  [font="Courier New"]____________________________________________________________________________________________
  Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 14, 2014 at 6:47 pm

  #1689267

  Here is an alternative that gives a bit more info.

  http://jasonbrimhall.info/2011/11/21/table-space-cs-part-deux/

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events