How to get the size of all tables in MSSQL

To get the size of all tables using MSSQL you can use the snippet below.

Sample MSSQL

SELECT 
    tab.NAME AS [Tablename],
    s.Name AS [Schema Name],
    part.rows AS [Count of Rows],
    SUM(a.total_pages) * 8 AS [Total Space in KB], 
    SUM(a.used_pages) * 8 AS [Used Space in KB], 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8  AS [Unused Space in KB]
FROM sys.tables tab
INNER JOIN sys.indexes idx ON tab.OBJECT_ID = idx.object_id
INNER JOIN sys.partitions part ON idx.object_id = part.OBJECT_ID AND idx.index_id = part.index_id
LEFT OUTER JOIN sys.schemas s ON tab.schema_id = s.schema_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
WHERE tab.NAME NOT LIKE 'dt%' AND tab.is_ms_shipped = 0 AND idx.OBJECT_ID > 255 
GROUP BY tab.Name, s.Name, part.Rows
ORDER BY 4 DESC

How to list all Databases in MSSQL

To list all Databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT name FROM sys.databases

SQL Server Version newer than MS SQL 2000 also support these StoredProcedures.

EXEC sp_databases

or

EXEC sp_helpdb

How to list last backup date of all databases in MSSQL

To list last backup date of all databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT db.name AS [Databasename], 
case when	MAX(bck.backup_finish_date) is NULL then 
				'No Backup' 
			else 
				convert(varchar(100), MAX(bck.backup_finish_date)) 
end AS [DateOfLastBackup]
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset bck 
	ON db.name = bck.database_name AND bck.type = 'D' WHERE db.database_id NOT IN (2) 
GROUP BY db.name
ORDER BY 2 ASC