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:
<>