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

Curses work by shouting - How to avoid collateral damage?

Where in the Bible does the greeting ("Dominus Vobiscum") used at Mass come from?

Is it okay / does it make sense for another player to join a running game of Munchkin?

There is only s̶i̶x̶t̶y one place he can be

What is the intuitive meaning of having a linear relationship between the logs of two variables?

What is the oldest known work of fiction?

Teaching indefinite integrals that require special-casing

when is out of tune ok?

How to prove that the query oracle is unitary?

Why "be dealt cards" rather than "be dealing cards"?

Why Were Madagascar and New Zealand Discovered So Late?

Time travel short story where a man arrives in the late 19th century in a time machine and then sends the machine back into the past

Hostile work environment after whistle-blowing on coworker and our boss. What do I do?

Why does John Bercow say “unlock” after reading out the results of a vote?

How does it work when somebody invests in my business?

Unattended/Unattended to?

Should my PhD thesis be submitted under my legal name?

Best way to store options for panels

How does a character multiclassing into warlock get a focus?

Select empty space and change color in vector

How do I define a right arrow with bar in LaTeX?

Hide Select Output from T-SQL

Modify casing of marked letters

Your magic is very sketchy



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













3















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.









share|improve this question









New contributor




John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
























    3















    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.









    share|improve this question









    New contributor




    John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      3












      3








      3








      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.









      share|improve this question









      New contributor




      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      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






      share|improve this question









      New contributor




      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited 1 hour ago









      MDCCL

      6,85331745




      6,85331745






      New contributor




      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 hours ago









      John ThomasJohn Thomas

      211




      211




      New contributor




      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.




















          2 Answers
          2






          active

          oldest

          votes


















          3














          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 ║
          ╚══════╩══════╩═══════════╩═════════╝




          share

























          • 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?)

            – John Thomas
            2 hours ago











          • 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/…

            – John Thomas
            1 hour ago


















          1














          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'





          share|improve this answer






















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



            );






            John Thomas is a new contributor. Be nice, and check out our Code of Conduct.









            draft saved

            draft discarded


















            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









            3














            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 ║
            ╚══════╩══════╩═══════════╩═════════╝




            share

























            • 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?)

              – John Thomas
              2 hours ago











            • 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/…

              – John Thomas
              1 hour ago















            3














            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 ║
            ╚══════╩══════╩═══════════╩═════════╝




            share

























            • 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?)

              – John Thomas
              2 hours ago











            • 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/…

              – John Thomas
              1 hour ago













            3












            3








            3







            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 ║
            ╚══════╩══════╩═══════════╩═════════╝




            share















            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 ║
            ╚══════╩══════╩═══════════╩═════════╝





            share













            share


            share








            edited 2 hours ago

























            answered 2 hours ago









            Max VernonMax Vernon

            51.9k13114230




            51.9k13114230












            • 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?)

              – John Thomas
              2 hours ago











            • 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/…

              – John Thomas
              1 hour ago

















            • 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?)

              – John Thomas
              2 hours ago











            • 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/…

              – John Thomas
              1 hour ago
















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

            – John Thomas
            2 hours ago





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

            – John Thomas
            2 hours ago













            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/…

            – John Thomas
            1 hour ago





            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/…

            – John Thomas
            1 hour ago













            1














            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'





            share|improve this answer



























              1














              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'





              share|improve this answer

























                1












                1








                1







                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'





                share|improve this answer













                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'






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 2 hours ago









                PadwanPadwan

                1916




                1916




















                    John Thomas is a new contributor. Be nice, and check out our Code of Conduct.









                    draft saved

                    draft discarded


















                    John Thomas is a new contributor. Be nice, and check out our Code of Conduct.












                    John Thomas is a new contributor. Be nice, and check out our Code of Conduct.











                    John Thomas is a new contributor. Be nice, and check out our Code of Conduct.














                    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.




                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    Oświęcim Innehåll Historia | Källor | Externa länkar | Navigeringsmeny50°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.2213950°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.221393089658Nordisk familjebok, AuschwitzInsidan tro och existensJewish Community i OświęcimAuschwitz Jewish Center: MuseumAuschwitz Jewish Center

                    Valle di Casies Indice Geografia fisica | Origini del nome | Storia | Società | Amministrazione | Sport | Note | Bibliografia | Voci correlate | Altri progetti | Collegamenti esterni | Menu di navigazione46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)Sito istituzionaleAstat Censimento della popolazione 2011 - Determinazione della consistenza dei tre gruppi linguistici della Provincia Autonoma di Bolzano-Alto Adige - giugno 2012Numeri e fattiValle di CasiesDato IstatTabella dei gradi/giorno dei Comuni italiani raggruppati per Regione e Provincia26 agosto 1993, n. 412Heraldry of the World: GsiesStatistiche I.StatValCasies.comWikimedia CommonsWikimedia CommonsValle di CasiesSito ufficialeValle di CasiesMM14870458910042978-6

                    Typsetting diagram chases (with TikZ?) Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)How to define the default vertical distance between nodes?Draw edge on arcNumerical conditional within tikz keys?TikZ: Drawing an arc from an intersection to an intersectionDrawing rectilinear curves in Tikz, aka an Etch-a-Sketch drawingLine up nested tikz enviroments or how to get rid of themHow to place nodes in an absolute coordinate system in tikzCommutative diagram with curve connecting between nodesTikz with standalone: pinning tikz coordinates to page cmDrawing a Decision Diagram with Tikz and layout manager