Displaying the order of the columns of a tableWhy should I create an ID column when I can use others as key fields?How do you create a relationship to a non-primary key in SQL Server?Oracle GoldenGate add trandata errorsError while executing SSIS package which contains Script component through SQL Server Agent JobShould I mark a composite index as unique if it contains the primary key?Can I rely on reading SQL Server Identity values in order?Order by custom filter without certain dataSQL Server query problem when selecting data from child table based on column in parent tableQuery runs slowly when a non-indexed column is added to the WHERE clauseProper table design for sparse primary key
Re-entry to Germany after vacation using blue card
Why was the Spitfire's elliptical wing almost uncopied by other aircraft of World War 2?
What happened to Captain America in Endgame?
All ASCII characters with a given bit count
Showing a welcome screen once per day
Read line from file and process something
Difference between did and does
What is meant by "Prämie" in this letter? Do I have to pay it or it is just a reminder?
How does Captain America channel this power?
Why do games have consumables?
Is there any official lore on the Far Realm?
Who was the lone kid in the line of people at the lake at the end of Avengers: Endgame?
Can't get 5V 3A DC constant
How can I practically buy stocks?
Pre-plastic human skin alternative
As an international instructor, should I openly talk about my accent?
How did Captain America use this in Avengers: Endgame?
Latex syntax: parenthesis for makebox(0,0)
Initiative: Do I lose my attack/action if my target moves or dies before my turn in combat?
I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?
Multiple options vs single option UI
Can I criticise the more senior developers around me for not writing clean code?
What is the optimal strategy for the Dictionary Game?
Farming on the moon
Displaying the order of the columns of a table
Why should I create an ID column when I can use others as key fields?How do you create a relationship to a non-primary key in SQL Server?Oracle GoldenGate add trandata errorsError while executing SSIS package which contains Script component through SQL Server Agent JobShould I mark a composite index as unique if it contains the primary key?Can I rely on reading SQL Server Identity values in order?Order by custom filter without certain dataSQL Server query problem when selecting data from child table based on column in parent tableQuery runs slowly when a non-indexed column is added to the WHERE clauseProper table design for sparse primary key
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?
select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
CustomerAddress varchar(255),
EnrollmentDate date
)
Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:
Column name Data type Description
----------- --------- ----------------------------------------------
column_id: int ID of the column. Is unique within the object.
Column IDs might not be sequential.
sql-server sql-server-2016
edited Mar 26 at 21:31
MDCCL
6,88331845
add a comment |
I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?
select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
CustomerAddress varchar(255),
EnrollmentDate date
)
Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:
Column name Data type Description
----------- --------- ----------------------------------------------
column_id: int ID of the column. Is unique within the object.
Column IDs might not be sequential.
sql-server sql-server-2016
edited Mar 26 at 21:31
MDCCL
6,88331845
add a comment |
I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?
select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
CustomerAddress varchar(255),
EnrollmentDate date
)
Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:
Column name Data type Description
----------- --------- ----------------------------------------------
column_id: int ID of the column. Is unique within the object.
Column IDs might not be sequential.
sql-server sql-server-2016
edited Mar 26 at 21:31
MDCCL
6,88331845
I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?
select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
CustomerAddress varchar(255),
EnrollmentDate date
)
Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:
Column name Data type Description
----------- --------- ----------------------------------------------
column_id: int ID of the column. Is unique within the object.
Column IDs might not be sequential.
sql-server sql-server-2016
sql-server sql-server-2016
edited Mar 26 at 21:31
MDCCL
6,88331845
edited Mar 26 at 21:31
MDCCL
6,88331845
edited Mar 26 at 21:31
MDCCL
6,88331845
edited Mar 26 at 21:31
MDCCL
6,88331845
edited Mar 26 at 21:31
MDCCL
6,88331845
6,88331845
asked Mar 26 at 20:24
user175410
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.
As the documentation states, column_id values may not be sequential if you drop a column from a table.
You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.
Consider a quick example:
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
c1 int
, c2 int
, c3 int
, c4 int
);
ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);
SELECT o.name
, c.name
, c.column_id
, ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'
The output looks like:
╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
add a comment |
Just to propose an additional answer that will tell you the actual column position instead of column_id
select column_name, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'
answered Mar 26 at 20:38
PadwanPadwan
32619
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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233179%2fdisplaying-the-order-of-the-columns-of-a-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.
As the documentation states, column_id values may not be sequential if you drop a column from a table.
You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.
Consider a quick example:
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
c1 int
, c2 int
, c3 int
, c4 int
);
ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);
SELECT o.name
, c.name
, c.column_id
, ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'
The output looks like:
╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
add a comment |
column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.
As the documentation states, column_id values may not be sequential if you drop a column from a table.
You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.
Consider a quick example:
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
c1 int
, c2 int
, c3 int
, c4 int
);
ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);
SELECT o.name
, c.name
, c.column_id
, ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'
The output looks like:
╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
add a comment |
column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.
As the documentation states, column_id values may not be sequential if you drop a column from a table.
You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.
Consider a quick example:
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
c1 int
, c2 int
, c3 int
, c4 int
);
ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);
SELECT o.name
, c.name
, c.column_id
, ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'
The output looks like:
╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.
As the documentation states, column_id values may not be sequential if you drop a column from a table.
You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.
Consider a quick example:
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
c1 int
, c2 int
, c3 int
, c4 int
);
ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);
SELECT o.name
, c.name
, c.column_id
, ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'
The output looks like:
╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
edited Mar 26 at 20:43
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
answered Mar 26 at 20:34
Max VernonMax Vernon
52.7k13115232
52.7k13115232
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
add a comment |
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?)
– user175410
Mar 26 at 20:58
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/…
– user175410
Mar 26 at 21:03
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly.
– Max Vernon
Mar 27 at 13:30
add a comment |
Just to propose an additional answer that will tell you the actual column position instead of column_id
select column_name, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'
answered Mar 26 at 20:38
PadwanPadwan
32619
add a comment |
Just to propose an additional answer that will tell you the actual column position instead of column_id
select column_name, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'
answered Mar 26 at 20:38
PadwanPadwan
32619
add a comment |
Just to propose an additional answer that will tell you the actual column position instead of column_id
select column_name, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'
answered Mar 26 at 20:38
PadwanPadwan
32619
Just to propose an additional answer that will tell you the actual column position instead of column_id
select column_name, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'
answered Mar 26 at 20:38
PadwanPadwan
32619
answered Mar 26 at 20:38
PadwanPadwan
32619
answered Mar 26 at 20:38
PadwanPadwan
32619
answered Mar 26 at 20:38
PadwanPadwan
32619
32619
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233179%2fdisplaying-the-order-of-the-columns-of-a-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
