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