SQL Server Performance

Retrieve Columns

Discussion in 'General Developer Questions' started by Madhivanan, May 9, 2005.

  1. Madhivanan Moderator

    Is there a query that will retrun column names as seperate columns?
    I have a table test having the columns id, name and spec
    I can write a query

    Select name from syscolumns order by colorder

    but the output is
    id
    name
    spec

    but my desired output woulld be
    id name spec



    Madhivanan

    Failing to plan is Planning to fail
  2. Adriaan New Member

    Do you need this for building queries? Then in QA, open the database, go to the User Tables, open the table in question, then click-and-drag the "Columns" icon onto you query window. This will print a comma-separated list of column names.

    If you need the column names in your client app, you can run through the Fields collection of your recordset object and retrieve the Name property for each field. Also check if by any chance your control has a property to show/hide the column names.

    One final option in QA: change the query output to Text, and run this:
    SELECT * FROM TEST WHERE id IS NULL
  3. Madhivanan Moderator

    Adriaan, I dont find User Tables option in QA

    quote:
    One final option in QA: change the query output to Text, and run this:
    SELECT * FROM TEST WHERE id IS NULL
    Yes this is one of the options. But my expected outcode is
    col1 col2 col3
    id name spec



    Madhivanan

    Failing to plan is Planning to fail
  4. Adriaan New Member

    quote:Originally posted by Madhivanan

    Adriaan, I dont find User Tables option in QA
    Which version of SQL Server are you working with? In QA for SQL 2000, in the Object Browser, expand the database. This expands to a couple of folders, and the first folder should be User Tables. Expand that folder, and you get the list of the user tables. Again, expand the user table that you need, and this again expands to a couple of folders, and the first folder should be Columns. Click-and-drag the Columns folder onto the query window.

    quote:
    quote:One final option in QA: change the query output to Text, and run this:
    SELECT * FROM TEST WHERE id IS NULL
    Yes this is one of the options. But my expected outcode is
    col1 col2 col3
    id name spec
    The best I can come up with is:

    DECLARE @Test VARCHAR(8000)

    SET @Test = ''

    SELECT
    @Test = @Test
    + CASE WHEN LEN(@Test) = 0 THEN 'SELECT TOP 1 ' ELSE ', ' END
    + CHAR(39) + name + CHAR(39) + ' Col' + CAST(colorder AS VARCHAR(10))
    from syscolumns
    WHERE id = object_id('test')
    order by colorder

    SET @Test = @Test + ' FROM syscolumns'

    EXEC (@Test)
    You may run into problems when the query statement exceeds 8000 characters.
  5. Madhivanan Moderator

    Yes, This is what I wanted. Thanks Adriaan


    Madhivanan

    Failing to plan is Planning to fail

Share This Page