How can you list all SQL Server tables, their columns, and column data types using Powershell?

Question

I'm trying to do documentation on an SQL server 2008 database. This includes listing all tables, their columns, and the column types. I've been so far as to use "get-childitem | get-member" while browsing the table, the column, and the column's extended properties, and none of them return the data type of the columns. Is there a way to list the data types of columns in powershell?

Answer 1

Sure, take a look at the SQL Server 2008 PowerShell provider over at:

http://msdn.microsoft.com/en-us/library/cc281954.aspx

-Oisin

Answer 2

From a table's Columns "directory", you can get the column types like this:

PS> gci | select Name, DataType