Monday, May 14, 2012

Powershell Listing SQL Table Columns

Powershell has an awesome utility called sqlps that both lets you execute sql statements against a database, and implements a file system provider over SQL.

One of the things I use this for all the time is to inspect the columns of a table.  Management Studio's tree view is terrible for this, especially compared to the flexibility of powershell which allows you to do things like:

  1. Sort the columns by name, or by data type
  2. Filter the columns by name, or by data type, or by nullable, etc
Here's a series of commands I use a lot that I thought was worth sharing:
  1. sqlps
  2. cd sql\localhost\default\databases\mydatabase\tables\schema.table\columns
  3. ls | where { $_.Name -notlike 'ColPrefix*' } |  select Name, @{Name="Type"; Expression={"$($_.DataType.Name)($($_.DataType.MaximumLength))"}}, Nullable
That will display all the columns that DO NOT have a name starting with ColPrefix and will show you each columns Name, Data Type (formatted like "nvarchar(255)"), and whether it allows nulls.

Enjoy!

No comments:

Post a Comment