Ashish Sharma

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.[na...

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 -ServerInstan...