Sfrgttk

How can I get players to stop ignoring or overlooking the plot hooks I'm giving them?

PTIJ: Should I kill my computer after installing software?

Difference on montgomery curve equation between EFD and RFC7748

Do f-stop and exposure time perfectly cancel?

How to draw cubes in a 3 dimensional plane

What's wrong with this bogus proof?

Was Luke Skywalker the leader of the Rebel forces on Hoth?

Good for you! in Russian

How does one describe somebody who is bi-racial?

Does this video of collapsing warehouse shelves show a real incident?

Reverse string, can I make it faster?

Why was Goose renamed from Chewie for the Captain Marvel film?

At what distance can a bugbear, holding a reach weapon, with the Polearm Master feat, get their Opportunity Attack?

How strictly should I take "Candidates must be local"?

Why is computing ridge regression with a Cholesky decomposition much quicker than using SVD?

How can The Temple of Elementary Evil reliably protect itself against kinetic bombardment?

Makefile strange variable substitution

What's the "normal" opposite of flautando?

Can one live in the U.S. and not use a credit card?

Counting all the hearts

Could you please stop shuffling the deck and play already?

Can Mathematica be used to create an Artistic 3D extrusion from a 2D image and wrap a line pattern around it?

Declaring and defining template, and specialising them

How are showroom/display vehicles prepared?

How to get table columns with datatypes (and lengths) using PowerShell?

Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-querySQL Server SMO Method “Discover()”SQL Server SMO and PowerShell formattingSeek predicate not using all available columnsIterating over the output of a SQL query with PowerShellRunning sp_AskBrent with @ExpertMode = 1 in PowerShellHow do I enable both PowerShell Remoting and SPN for SQL Server Reporting?SQL Server 2016 Powershell: Restore-SqlDatabase with -ReplaceDatabase and -RelocateFilePutting together Powershell script to document SQL Server with InstancesAdd-SqlAvailabilityDatabase : The connection to the primary replica is not active

2

3

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?

sql-server sql-server-2008 powershell

share|improve this question

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

migrated from stackoverflow.com Oct 3 '11 at 11:36

This question came from our site for professional and enthusiast programmers.

add a comment | 

2

3

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?

sql-server sql-server-2008 powershell

share|improve this question

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

migrated from stackoverflow.com Oct 3 '11 at 11:36

This question came from our site for professional and enthusiast programmers.

add a comment | 

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?

sql-server sql-server-2008 powershell

share|improve this question

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

#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

share|improve this question

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

share|improve this question

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

asked Sep 30 '11 at 13:04

jrarajrara

2,363174862

3 Answers
3

active

oldest

votes

5

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

share|improve this answer

answered Sep 30 '11 at 13:37

RynantRynant

16613

add a comment | 

3

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'

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Sep 30 '11 at 13:18

DirtyPaws

add a comment | 

1

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)

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f6437%2fhow-to-get-table-columns-with-datatypes-and-lengths-using-powershell%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

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

share|improve this answer

answered Sep 30 '11 at 13:37

RynantRynant

16613

add a comment | 

5

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

share|improve this answer

answered Sep 30 '11 at 13:37

RynantRynant

16613

add a comment | 

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

share|improve this answer

answered Sep 30 '11 at 13:37

RynantRynant

16613

#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

share|improve this answer

answered Sep 30 '11 at 13:37

RynantRynant

16613

answered Sep 30 '11 at 13:37

RynantRynant

16613

answered Sep 30 '11 at 13:37

RynantRynant

16613

3

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'

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Sep 30 '11 at 13:18

DirtyPaws

add a comment | 

3

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'

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Sep 30 '11 at 13:18

DirtyPaws

add a comment | 

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'

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Sep 30 '11 at 13:18

DirtyPaws

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'

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Sep 30 '11 at 13:18

DirtyPaws

edited 1 min ago

John Baughman

1051

edited 1 min ago

John Baughman

1051

1

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)

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

add a comment | 

1

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)

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

add a comment | 

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)

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

#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)

share|improve this answer

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

edited 1 min ago

John Baughman

1051

edited 1 min ago

John Baughman

1051

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112

answered Jan 28 '16 at 11:11

Jonathan H.Jonathan H.

1112