SQL PowerShell – Enchancing Listing your DB Table Information

I love the PowerShell collaboration in the IT community.  One of my twitter followers reminded me about the “Format-Table” command  that allows you to group your collection using the parameter “-GroupBy” which work beautifully.  There’s no need to add the “ForEach” command.  I trully forgot to the check for this parameter. 

This change in my script will saves be  a few more lines of code.and here’s the final result.  Here’s the script:

[sourcecode language=”powershell” 1=”wraplines"false"”]
## – Stored T-SQL modified script into a string variable
$sqlQuery = @"
SELECT
s.name+’.’+OBJECT_NAME(c.OBJECT_ID) as SchemaTableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables as t2 on t2.object_id = c.object_id
JOIN sys.schemas as s on s.schema_id = t2.schema_id
ORDER BY c.OBJECT_ID;
"@

## – Load the SQLPS module for Denali ( for 2008/200R2 is SQLPS)
Import-Module SQLPSv2
$SavedResults1 = Invoke-SQLCmd -ServerInstance "ISO-DESKTOP-65" -Database "ReportServer" -Query $sqlQuery
$SavedResults1 | ft -auto -GroupBy SchemaTableName
[/sourcecode]

And, here’s the sample PowerShell Console result:

This is a good example of how PowerShell can keep simplifying your script as yiou continue to grow.

Especial THANKS to David Moravec (http://www.powershell.cz) for tweet to giving me the tip to enhance this script.   Now, you can download both versions:

<>