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