SQL query to understand the names of all the available tables , number of records in these tables and size of these tables

• krishnaprasad_mt

  Grasshopper

  Points: 13

  More actions

  August 31, 2014 at 6:39 am

  #290295

  Hi

  Please help me with a SQL query to understand the names of all the available tables , number of records in these tables and size of these tables ? Many Thanks

  Regards,

  Prasad

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  August 31, 2014 at 7:07 am

  #1742113

  Quick thought, look at sys.partitions and sys.tables

  😎

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 31, 2014 at 7:25 am

  #1742114

  For a quick overview you could use the Standard Reports from SSMS

  Right click on a database -> Reports -> Standard Reports -> Disc Usage by Tables

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• caffeinated

  Ten Centuries

  Points: 1271

  More actions

  August 31, 2014 at 11:53 pm

  #1742146

  Here's a quick query using system tables and reference:

  INFORMATION_SCHEMA: http://msdn.microsoft.com/en-us/library/ms186778.aspx

  Database Object Views: http://msdn.microsoft.com/en-us/library/ms189783.aspx

  SELECT

  table_name = t.name

  ,table_rowcnt = SUM(p.rows)

  FROM sys.tables t

  INNER JOIN sys.partitions p on p.object_id = t.object_id

  INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

  WHERE t.is_ms_shipped = 0 and p.index_id in (1,0)

  GROUP by

  s.name, t.name

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  September 1, 2014 at 9:32 am

  #1742244

  krishnaprasad_mt (8/31/2014)

  ---

  Hi

  Please help me with a SQL query to understand the names of all the available tables , number of records in these tables and size of these tables ? Many Thanks

  Regards,

  Prasad

  Here you go

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  GO

  SELECTs.name

  , OBJECT_NAME(o.OBJECT_ID)AS TableName

  , ISNULL(i.name, 'HEAP')AS IndexName

  , i.index_idAS IndexID

  , CASE i.[type]

  WHEN 0 THEN 'HEAP'

  WHEN 1 THEN 'Clustered'

  WHEN 2 THEN 'NonClustered'

  WHEN 3 THEN 'XML'

  WHEN 4 THEN 'Spatial'

  ENDAS IndexType

  , i.is_disabledAS IsDisabled

  , i.data_space_id

  , CASE

  WHEN i.data_space_id > 65600 THEN ps.name

  ELSE f.name

  ENDAS FGName

  , p.partition_numberAS PartitionNo

  , p.[rows]AS [RowCnt]

  , p.data_compression_descAS CompressionType

  , au.type_descAS AllocType

  , au.total_pages / 128AS TotalMBs

  , au.used_pages/ 128AS UsedMBs

  , au.data_pages/ 128AS DataMBs

  FROM sys.indexes i

  LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

  LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

  INNER JOIN sys.objects o ON i.object_id = o.object_id

  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

  INNER JOIN sys.allocation_units au ON

  CASE

  WHEN au.[type] IN (1,3) THEN p.hobt_id

  WHEN au.[type] = 2 THEN p.partition_id

  END = au.container_id

  WHERE o.is_ms_shipped <> 1

  ORDER BY TotalMBs DESC

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉