Get Size of all Tables & Indexes in SQL Database

Size of Table including Indexes:

This SQL query will provide the size (in KB) of each table including all the indexes on that table:

SELECT
t.[Name] AS TableName,
p.[rows] AS [RowCount],
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB
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
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.[Name], p.[Rows]
ORDER BY t.[Name]

Size of each Index

This SQL query will provide the size of index in the database:

SELECT
i.[name] AS IndexName,
t.[name] AS TableName,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] 
AND s.[index_id] = i.[index_id]
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
GROUP BY i.[name], t.[name]
ORDER BY i.[name], t.[name]

Connect with Microsoft SQL Databases using PowerShell

The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. Invoke-SqlCmd is versatile because it can be used on its own or from within the SQL PowerShell Provider. When used within the SQL Server Provider it is possible to leave off some of the parameters, depending on where you have navigated. When navigating an instance, you can leave off the -ServerInstance parameter.

Here, we will use this powershell cmdlet to fetch data from SQL Server database. Let\’s assume, we have table [StudentDetails] as given below:

Before running this script, make sure you have sqlserver module installed

To install sqlserver module use: Install-Module -Name sqlserver

Run the below powershell command:

$sqlserver= \"sqlserver name\"
$sqldb=\"database name\"
$sqluser=\"SQL Username\"
$sqlpass=\"SQL Password\"
$sqlquery = \"Select * from StudentDetails\"
$result = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $sqldb -Query $sqlquery -Username $sqluser -Password $sqlpass
$result

Refer this for more information related to Invoke-Sqlcmd cmdlet : https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

I hope you found this useful. Happy scripting. 🙂