Professional Web Applications Themes

Getting the Columns comma separated - Microsoft SQL / MS SQL Server

I have many table with more than 250 columns. I have to dynamically generate script to build the column names in the select statement. Due to the varchar(8000) limit half of the column names are getting truncated. What is the best of doing it. My sample script: Declare tableNames table (id int identity, tblname varchar(100)) -- has all the tables Declare ColumnNames table (id int identity, tblname varchar(100),colname varchar(8000))--return the table Declare ctr integer--Counter for Table Declare Max integer -- Max no. of Tables Declare tblname varchar(50) Declare Str varchar(8000) insert into tableNames(tblname) select distinct(TableName) from #Tables set ctr = ...

  1. #1

    Default Getting the Columns comma separated

    I have many table with more than 250 columns.
    I have to dynamically generate script to build the column names in the
    select statement. Due to the varchar(8000) limit half of the column
    names are getting truncated. What is the best of doing it.

    My sample script:

    Declare tableNames table (id int identity, tblname varchar(100)) --
    has all the tables
    Declare ColumnNames table (id int identity, tblname
    varchar(100),colname varchar(8000))--return the table
    Declare ctr integer--Counter for Table
    Declare Max integer -- Max no. of Tables
    Declare tblname varchar(50)
    Declare Str varchar(8000)

    insert into tableNames(tblname) select distinct(TableName) from
    #Tables
    set ctr = 1
    select Max = max(id) from tableNames
    SET NOCOUNT ON
    while ( ctr < = Max )
    begin

    select tblname = tblname from tableNames where id = ctr

    SET Str =''
    Select Str =Str + ', '+ Column_Name From
    Information_Schema.Columns
    Where Table_name = tblname
    Order By oRDINAL_pOSITION
    insert into ColumnNames(tblname,colname) select
    tblname,Substring(Str, 3, LEN(Str)-2)

    set ctr = ctr + 1
    end
    select * from ColumnNames -- your result


    Will be very thankful

    Thanks
    Babu
    Babu Guest

  2. #2

    Default Re: Getting the Columns comma separated


    Iam not so clear with the reply.
    Can you explain it detaily or send me the script pls.

    Thanks
    Ganesh



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    ganesh Guest

  3. #3

    Default Re: Getting the Columns comma separated

    If your using dynamic sql and the commands are too long to fit into one
    varchar you can split them up into multiple varchars and execute it as so:

    DECLARE Var1 VARCHAR(8000),Var2 VARCHAR(8000),Var3 VARCHAR(8000)
    SET Var1 = 'SELECT Col1, Col2.......'
    SET Var2 = 'FROM Table1, Table 2..........'
    SET Var3 = 'WHERE .....................'

    EXEC (Var1 + Var2 + Var3)

    Maybe this will help:
    http://www.algonet.se/~sommar/dynamic_sql.html Dynamic SQL


    --

    Andrew J. Kelly
    SQL Server MVP


    "ganesh babu" <com> wrote in message
    news:O87t1%phx.gbl... 


    Andrew Guest

Similar Threads

  1. Exporting Metadata (caption information) fromJPEGS to a comma separated value (CSV) file
    By Jennifer_Preissel@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: November 6th, 09:28 AM
  2. how do you create a comma separated list of the columnnames in a table?
    By ionic77 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: April 28th, 07:14 PM
  3. How to handle a comma separated file with imbedded commas
    By James Edward Gray II in forum PERL Beginners
    Replies: 0
    Last Post: September 2nd, 07:24 PM
  4. Replies: 0
    Last Post: July 15th, 06:44 PM
  5. comma separated to SQL Server DB table
    By Aaron Bertrand [MVP] in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139