Query -Database Size in GB

            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'