Query to get Database size and table size.

Query to get Database size in GB.

SELECT
 D.NAME,
 Cast((SUM(MF.SIZE) * 8.00 /1024.00/1024.00)as Numeric(18,2)) AS [SIZE (In GB)]
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
 ON D.DATABASE_ID = MF.DATABASE_ID
 WHERE D.DATABASE_ID > 4
 GROUP BY D.NAME
 ORDER BY [SIZE (In GB)] DESC

Query to get Table size in GB with no of rows.

SELECT SCHEMA_NAME(T.Schema_id) + '.' + T.Name
 AS
 Tablename,
 SUM(
		CASE
			WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
			ELSE 0
		END
	   )
 AS Rowcounts,
 CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(
 18, 3)) AS
 Totalspacegb
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
 AND I.Index_id = P.Index_id
 INNER JOIN SYS.Allocation_units A
 ON P.Partition_id = A.Container_id
 INNER JOIN SYS.Filegroups Fg
 ON I.Data_space_id = Fg.Data_space_id
 INNER JOIN SYS.Database_files Df
 ON Df.Data_space_id = Fg.Data_space_id
WHERE T.Is_ms_shipped = 0
 AND I.Object_id > 255
GROUP BY SCHEMA_NAME(T.Schema_id) + '.' + T.Name
ORDER BY Totalspacegb DESC

Query to get Service Broker Queue size in GB with no of rows.

Select OBJECT_NAME(IT.parent_object_id),SUM(
 CASE
 WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
 ELSE 0
 END
 )
 AS Rowcounts,CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb
From sys.partitions P
Inner Join sys.internal_tables IT On IT.OBJECT_ID = P.OBJECT_ID
INNER JOIN SYS.Allocation_units A
ON P.PARTITION_ID = A.Container_id
Where P.index_id In (1, 0)
Group By OBJECT_NAME(IT.parent_object_id)
Order by 3 DESC

Regards,
Nirav Gajjar