Query to get Database size in GB FORMAT :
SELECT dbs.NAME, CAST((SUM(mFiles.SIZE) * 8 / 1024.00 / 1024.00) AS NUMERIC(18,2)) AS [DB SIZE (In GB)]
FROM
SYS.MASTER_FILES mFiles INNER JOIN SYS.DATABASES dbs
ON dbs.DATABASE_ID = mFiles.DATABASE_ID
WHERE dbs.DATABASE_ID > 4 -- FILTER OUT THE DATABSES AS "master", -- "tempdb", "model" AND "msdb"
GROUP BY dbs.NAME
ORDER BY [DB SIZE (In GB)]
SELECT d.NAME ,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs ,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.NAME ORDER BY d.NAME
-------database size mb & gb with database create date
SELECT d.NAME
,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs,
convert(varchar, d.create_date, 107) as create_date
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME,d.create_date
ORDER BY d.NAME,d.create_date
Query to get tables size in GB with number of Rows against the Database:
SELECT s.name + '.' + t.Name AS [Table Name], part.rows AS [Total Rows In Table - Modified],
CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
AS [Table's Total Space In GB]
FROM
SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
AND idx.Index_id = part.Index_id
INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC
----------------------------- Following query will give you list of all databases and associated files and sizes :-----------------------------
SELECT sd.NAME
,smf.NAME
,smf.type_desc
,(CAST(smf.size AS FLOAT) * 8096) AS SizeBytes
,(CAST(smf.size AS FLOAT) * 8096) / (1024) AS SizeKB
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024) AS SizeMB
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024 * 1024) AS SizeGB
,smf.physical_name
,sd.log_reuse_wait_desc
,sd.recovery_model_desc
,*
FROM sys.databases sd
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE
smf.type_desc IN (
'ROWS'
,'LOG'
)
ORDER BY sd.NAME DESC
-----------------------------------------------compatibility level multiple database ----
--mass change the compatibility level option for all instances and --databases that are not read only and have current comp. set to 80 or 90 EXEC sp_MSforeachdb ' use [?] if (select count(*) from sys.databases where compatibility_level <100 and name=''?'' and database_id>4 and is_read_only = 0 )=1 begin alter database [?] set compatibility_level = 100 PRINT ''compatibility changed to 100 for database = '' + DB_NAME() end'