getting column names for table using query

  • Hi ,

    I am using the following query to get the column names of a particular table.

    SELECT COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Categories'

    The problem is it gives the column names in several rows. What I want is the have all the columns in a single row like

    CategoryID CategoryName Description Picture

    It is just the transpose of the result. Is there a way to do this in sql?

    Thanks,

    Sridhar!!

  • Try something like this:

    Declare @Cols varchar(4096)

    Set @Cols = ''

    SELECT @Cols = @Cols + COLUMN_NAME + ' '

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Categories'

    Select RTrim(@Cols) As Columns

  • I think it's time for a shameless plug

    IF Object_id('ListTableColumns') > 0

    DROP FUNCTION ListTableColumns

    GO

    CREATE FUNCTION dbo.ListTableColumns (@TableID as int)

    RETURNS varchar(8000)

    AS

    BEGIN

    Declare @Items as varchar(8000)

    SET @Items = ''

    SELECT

    @Items = @Items + C.Name + ', '

    FROMdbo.SysColumns C

    WHEREC.id = @TableID

    AND OBJECTPROPERTY(@TableID, 'IsTable') = 1

    ORDER BYC.Name

    SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))

    RETURN @Items

    END

    GO

    Select dbo.ListTableColumns(Object_id('SysObjects'))

    --base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype

    DROP FUNCTION ListTableColumns

  • Thanks Remi Gregoire. The whole purpose of displaying it single rows is to copy it and paste in the excel. so I want each column name in a separate column so that I can copy and paste in the excel. But I don't know whether it is possible.

    Thanks,

    Sridhar!!

  • Would be easy to write client side. It would only take to while loops... And would be much simpler than any tsql method out there (even if you just split on the comma).

  • Sridhar if that is your sole reason use Query Analyser.

    Use Tools=>Options=>results and Make results TAB delimited. Make Results Target to text.

    Then Execute

    SELECT * FROM TableName WHERE 1 = 2.

    Copy Paste the results to EXCEL you have only the Columns in each cell harizontally.

    You can copy paste the entire resultset like this.

     

    Regards,
    gova

  • What about something like this?

    declare @STR varchar(2000)

    set @STR = ''

    select @STR = @STR + '''' + c.name + ''' as ' + c.name + ','

    from syscolumns c

    where object_name(id) = 'Employees'

    order by colid asc

    set @STR = 'SELECT ' + LEFT(@str, LEN(@str)-1) + ' INTO #t   SELECT * FROM #t DROP TABLE #t'

    EXEC (@str)

     

    Thanks

    Greg

  • Nice trick .

  • No need to use dynamic sql here, no need for temp table either. Also, this doesn't work for all the tables in the same query and finally this solution has already been provided twice.

  • Thanks Remi for your position criticism.  Kinda remind of Celko.

    First of all, you are correct with the temp table.  I use a similar method to auto create a table so that I can use it later.  I just filled in column names for this thread.  And, I didn't see above where someone selected the data back out.  I see people creating comma separated lists, but when you select it, you get the results.  If in Grid results, easy to cut/paste to Excel.

  • Sorry, didn't mean to rant on you .

  • Grids will be easy to cut and paste. One problem is you will not get column names as headings. Easy way is make TAB delimitted you get data and headings as needed.

    Regards,
    gova

  • Thank you All

  • Govinn - I'm not able to recreate this (the QA trick...)

    Also, could you please tell me what "where 1 = 2" does ?!?! Thanks!







    **ASCII stupid question, get a stupid ANSI !!!**

  • 1=2 returns only the metadata, no select is actually done.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply