Ultimate MSSQL Databases Cheatsheet

SELECT 
t.name AS TableName,
s.name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN 
sys.schemas s ON t.schema_id = s.schema_id
WHERE 
t.name NOT LIKE 'dt%' 
AND t.is_ms_shipped = 0
AND i.object_id > 255 
GROUP BY 
t.name, s.name, p.rows
ORDER BY 
p.rows desc --TotalSpaceMB DESC

Source : Stackoverflow

2. Get All Database Info

SELECT
db.name AS [Database Name],
mf.name AS [Logical Name],
mf.type_desc AS [File Type],
mf.physical_name AS [Path],
CAST(
(mf.Size * 8
) / 1024.0 AS DECIMAL(18, 1)) AS [Initial Size (MB)],
'By '+IIF(
mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
(mf.growth * 8
) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth],
IIF(mf.max_size = 0, 'No growth is allowed', IIF(mf.max_size = -1, 'Unlimited', CAST(
(
CAST(mf.max_size AS BIGINT) * 8
) / 1024 AS VARCHAR(30))+' MB')) AS [MaximumSize]
FROM
sys.master_files AS mf
INNER JOIN sys.databases AS db ON
db.database_id = mf.database_id 

with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
order by DataFileSizeMB desc

SELECT 
[restore_date]
,[destination_database_name]
,[user_name]
,[backup_set_id]
,[restore_type]
,[replace]
,[recovery]
,[restart]
FROM [msdb].[dbo].[restorehistory] order by [restore_date] desc/* Your code... */

SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM 
msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 1=1
--and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date/* Your code... */