Get Total Rows and size for all tables in SQL Server

Here is the query to find total records for all tables along with size 

DECLARE @size VARCHAR(10)

DECLARE @dividedby INT

SET @size= 'MB'  -- change this to GB you'll get size in Gb otherwise  it will be in MBs

IF @size='GB'

SET @dividedby = 1048576

ELSE

BEGIN

SET @dividedby = 1024

SET @size='MB'

END

CREATE TABLE #temp (

TableName sysname ,

TotalRows BIGINT,

reserved_size VARCHAR(500),

data_size VARCHAR(500),

index_size VARCHAR(500),

unused_size VARCHAR(500))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.TableName,

a.TotalRows,

 CAST(ROUND((CAST(REPLACE(a.data_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Data size',

 CAST(ROUND((CAST(REPLACE(a.reserved_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Reserved size ',

 CAST(ROUND((CAST(REPLACE(a.index_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Index size',

 CAST(ROUND((CAST(REPLACE(a.unused_size, ' KB', '') AS DECIMAL(20,4))/@dividedby),4) AS NUMERIC(36,4)) 'Unused size ',

 @size 'size in '

FROM #temp a

ORDER BY  TotalRows DESC

DROP TABLE #temp

Get Number of tables in a database in SQL Server

Here is the query to find total number of tables in a specific database in SQL Server.

USE DBName

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE'

OR

SELECT COUNT(*) FROM sysobjects WHERE xtype = 'U'

DBName is the name of your database in which you want to find the total number of tables.