Making some addition to Pinal Dave T-SQL code provided in one of his blog on “List All The Column With Specific Data Types” we can use PowerShell to list our database table’s columns in an efficient way. But, for the information what I needed to produce I had to add a few things to Pinal’s code.
Keep in mind, PowerShell can assist the DBA in effectively providing better T-SQL results. Let me show you how we can leverage this T-SQL code with PowerShell.
Here’s the original SQL script executed in SSMS and the actual results shown in the following picture:
(Pinal Dave SQL Script)
This is a start but I needed a little more information, and a better way to display it.
First, I added two other ‘joins’ to the SQL script to include the ‘sys.tables’ and ‘sys.schemas’. Then, I customized the script a little more to get the results I wanted and ran it on my SSMS:
| 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; |
As you can see, now I included Schema with Table names and removed some columns. Now, let’s put PowerShell in action. I’m going to use my modified T-SQL script, create a simple PowerShell script and run it under SQL PowerShell.
Prerequisite for executing this script, you need to have:
-
SQL Server 2008 (or above)
-
Download Chad Miller SQLPS module for SQL Server 2008 and 2008 R2
-
Or, Download my version of SQLPSv2 for SQL Server ‘Denali’.
-
-
PowerShell 2.0
Using the any of the SQLPS modules, you can create a simple PowerShell script to generate our table columns information with a few lines of code:
-
Store the T-SQL script code in a string variable.
-
Load the SQL PowerShell Module using the “Import-Module” command.
-
Run the T-SQL query using the “Invoke-SQLCmd” to store the information in a variable.
-
Then, we can manipulate the results we just saved in a variable.
Here’s the script:
| $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; “@ Import-Module SQLPSv2 $SavedResults1 = Invoke-SQLCmd –ServerInstance “MAX-PCWIN1” –Database “Adventureworks” –Query $sqlQuery $SavedResults1 | ft –auto |
The last line of this script will list all content of your stored results and display it in a table format.
We got all tables listed one after another. So, we can add a few more lines of code to separate the tables:
| ## – First create a list of all the tables in the stored object ‘$SavedResults1’ $TableName = $SavedResults1 | select –unique SchemaTableName | Sort SchemaTableName ## – Verify all tables selected and in order $TableName |
Next step is to use a “ForEach-Object” command to help us break out the tables information individually, and display it on the PowerShell console:
| foreach($t in $TableName){ $SavedResults1 | where {$_.SchemaTableName -eq $t.SchemaTableName} | ` select SchemaTableName,ColumnName,SchemaName,TypeName,max_length | FT –Auto } |
Now, you can be creative in how this information can be displayed, or even exported to another file format such as a ‘CSV’ or ‘TXT’ type. Just try it!!
Here’s the complete simple PowerShell script:
Stay tuned for more!
To get script file, download here: