Xrhrft

I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far:

#Load PSSnapin

Add-PSSnapin *SQL*



#Get column names

$colNames = dir 'SQLSERVER:SQLMYCOMPUTERMYSQLINSTANCEDatabasesMYDATABASETables' | 

    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} | 

        ForEach-Object {$_.Columns} |

            Select-Object Name, DataType

$colNames

How to get also datatype lengths for columns?

The lengths are found at <Column>.Properties['Length'].Value, so you can select it like:

#Get column names

$colNames = dir 'SQLSERVER:SQLMYCOMPUTERMYSQLINSTANCEDatabasesMYDATABASETables' | 

    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} | 

        ForEach-Object {$_.Columns} |

            Select-Object Name, DataType, `

                @{Name='Length'; Expression = {$_.Properties['Length'].Value}}

$colNames

Unfortunately, I don't know the PowerShell syntax, but, heres the SQL for what you want:

SELECT 

    TableName = OBJECT_NAME(c.OBJECT_ID), 

    ColumnName = c.name,

    DataType = t.name, -- Type is an int in the columns table, this returns the type name.

    MaxLength = c.max_length -- Returns the max length of the column.

FROM 

    sys.columns AS c

JOIN 

    sys.types AS t 

ON c.user_type_id=t.user_type_id

WHERE 

    OBJECT_NAME(c.OBJECT_ID) = 'MYTABLE'

Thanks to DirtyPaws.
SQL Statement with Powershell Syntax:

#Credentials

$SQLServer = "SQL ServerName/IP Address"

$SQLDBName = "DatabaseName"

$uid ="MySqlUser"

$pwd = "MySqlUserPassword"



#Establish SQL Connection

$connectionString = "Server=$SQLServer;Database = $SQLDBName; User ID = $uid; Password = $pwd;"



$connection = New-Object System.Data.SqlClient.SqlConnection

$connection.ConnectionString = $connectionString

$connection.Open()



#Create SQL Statement

$query = "

SELECT 

    TableName = OBJECT_NAME(c.OBJECT_ID), 

    ColumnName = c.name,

    DataType = t.name, -- Type is an int in the columns table, this returns the type name.

    MaxLength = c.max_length -- Returns the max length of the column.

FROM 

    sys.columns AS c

JOIN 

    sys.types AS t 

ON c.user_type_id=t.user_type_id

WHERE 

    OBJECT_NAME(c.OBJECT_ID) = 'MyTableName'

"



#Add SQL Query to SQL Connection

$command = $connection.CreateCommand()

$command.CommandText = $query



#Execute SQL Query

$result = $command.ExecuteReader()



#Add result to DataTable Object and Display it

$table = new-object “System.Data.DataTable”

$table.Load($result)



Write-Host ($table | Format-Table | Out-String)

Write-Host ($table | Format-List | Out-String)