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:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
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:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
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)
I had a slightly different but very similar problem. I needed to find the data type and length for a known single column. The PowerShell needed to be robust enough to handle the precision and scale of decimal places. With a little bit of tweaking the PowerShell below could be modified to apply to the original question as well:
$ColumnName = 'Your Column Name';
dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables\dbo.TableName' | Select `
Name, `
DataType, `
@{Name='Length'; Expression = {$_.DataType.MaximumLength}},
@{Name='NumericPrecision'; Expression = {$_.DataType.NumericPrecision}}, `
@{Name='NumericScale'; Expression = {$_.DataType.NumericScale}} | ? Name -eq $ColumnName;
