SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    reading column name

    Hi there

    I have a table called specifications which has many columns. Each column represents a component on a bike.

    I want to display a table with 2 columns with the database column names on the left and the values on the right. So is it possible to read the column names from the SQL Server 2000 for my table?

    Many thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you cannot do this easily with raw sql

    are you using any kind of scripting language, like coldfusion, asp, or php?

    do it there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thanks for the reply.

    I've managed to get the column names in my table using the following query:
    Code:
    string getSpecs = "SELECT column_name from INFORMATION_SCHEMA.Columns WHERE table_name = 'Specs'";
    I'm just going to try and add the actual data once I've added some

    Cheers

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's a very good step, not too many people know about that

    the really cool part is that those INFORMATION SCHEMA tables are actually a part of the sql standard

    nice that microsoft implemented them, eh

    anyhow, there's a bit of a wee problem, in that you would still have to write code to match the column name with the value

    i hate writing code and avoid it if i can

    (aside: sql is not code)

    no, you can't do a join from there to the data table

    please keep me posted on how you do...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again. Well I haven't written the code to get the values yet, but I'm working on it. What I have done though which I think is quite a nifty feature is this....

    The query I posted above also returned the primary key and foreign key columns - specID and productID. So I used ordinal_position to skip the first 2 columns as follows:
    Code:
    SELECT * from INFORMATION_SCHEMA.Columns c, Specs s WHERE c.table_name = 'Specs' AND c.ordinal_position > 2
    Very useful

  6. #6
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, I'm stuck on this one I'm afraid. I'm using C#.NET and I'm stumped at the moment, but it is 2:00am so I may revisit tomorrow.


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
  •