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!