While working with Powershell I have begun to move away from using the SSMS GUI to get basic details on SQL instances. Management Studio is an excellent tool but I find myself keeping Powershell open and using it for quick queries to answer the basics. One of the basics is getting a list of Databases with their status, sizes and more in 1 query.
I tried using the “Get-SqlDatabase” one and it’s “WAY” slow for me for some reason. It also does not bring back exactly what I want either.
Get-SqlDatabase -ServerInstance $sqlinstance "MyServer\SQLInstance"
Instead I went back to the Invoke-Sqlcmd command and a simple SQL statement, I already have written, that can be run on any SQL instance. This statement will return the database details as well as the sizes and details on the data files too.
$sqlinstance = "MyServer\SQLInstance" $query = "SELECT db_name(db.database_id) AS [DB], db.[state_desc], db.create_date, db.[compatibility_level], db.recovery_model_desc, mf.[name] AS [Internal_Name], mf.[physical_name], mf.[type_desc], CAST(mf.size * 8.0/1024/1024 AS DECIMAL(8,2)) AS [Size_GB], CASE WHEN CAST(mf.max_size AS nvarchar(10)) = '-1' THEN 'UNLIMTED' ELSE CAST(CAST(mf.max_size * 8.0/1024/1024 AS DECIMAL(8,2)) AS nvarchar(50)) END AS [Max_Size_GB], CASE WHEN CAST(is_percent_growth AS nvarchar(10)) = '1' THEN CAST(mf.growth AS nvarchar(10))+'%' WHEN CAST(mf.growth AS nvarchar(10)) = 0 THEN NULL ELSE CAST(CAST(mf.growth * 8.0/1024 AS INT) AS nvarchar(10)) END AS [Growth_GB_MB] FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id" Invoke-Sqlcmd -ServerInstance $sqlinstance -Query $query | Out-Gridview ## format-table
Out-Gridview and Format-Table
Just to be ultra-efficient I decided to comment out the Format-Table commandlet and use the Gridviewer instead. Th e”Out-Gridview” will take your results (db tales com) and provide a small popup window that allows you to view, filter and sort the result set.
Try it out and see what you think, i’ve been using the GridView almost exclusively now.