select/get column names of a specified table

sql server

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 70911 Views - Last Post: 10 March 2010 - 03:46 AM Rate Topic: -----

#1 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

select/get column names of a specified table

Posted 12 December 2001 - 02:48 AM

hi there

i'm working on an sql server.

example: i got a table "user" with columns "usID","usNAME" and "usPWD"

i know it works, but i don't know the sql statement to SELECT all the column names of a specified table (here: "user")

SELECT * FROM sysobjects WHERE type = 'U' - with this statement i got every table in my database.. so i think it will work in a way like that..

do you have an idea?

thanks


Is This A Good Question/Topic? 0
  • +

Replies To: select/get column names of a specified table

#2 The Neoracle  Icon User is offline

  • Check, check, 1, 2.
  • member icon

Reputation: 21
  • View blog
  • Posts: 4,097
  • Joined: 30-March 01

Re: select/get column names of a specified table

Posted 12 December 2001 - 11:20 AM

SELECT "usID" FROM "user"

The syntax is the same as what you had before but instead if the * character (which means everything) you just specify what you want

Was This Post Helpful? 0
  • +
  • -

#3 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: select/get column names of a specified table

Posted 12 December 2001 - 01:19 PM

Quote

Quote: from The Neoracle on 12:20 pm on Dec. 12, 2001
SELECT "usID" FROM "user"

Except without the quotes around the table and column names. So if you want all the columns in the table "user" then do:
SELECT * FROM user

and add a WHERE clause if you like, and whatever else.

Was This Post Helpful? 0
  • +
  • -

#4 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:08 AM

arg... that's not what i wanted...

example: SELECT * FROM sysobjects WHERE type = 'U'  returns a recordset that consists of these entries:

name
----------------------
User
Articles
Links
Comments

so out put rs("name") while not EOF is "UserArticlesLinksComments"

- the table names in the database...

and if "user" consits of the attributes "usname, usid, uspwd" i want to have a sql query that returns

name
----------
usname
usid
uspwd


understand now?

btw... i know sql pretty good... but no idea about that...

(Edited by Quentin at 10:13 am on Dec. 13, 2001)

Was This Post Helpful? 0
  • +
  • -

#5 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:17 AM

oh, you're looking for the column *names*, not the column *contents*...

interesting. I've never done that before. I'll think about that... just out of curiosity, why would you need to do that? don't you know the names of all your table columns?
(if you don't, I worry about the application... ;)

Was This Post Helpful? 0
  • +
  • -

#6 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:21 AM

want to make an admin tool
and its very useful...

for example a data-manipulation-file... select your table, enter a sql statement and execute it... or list all your data of 1 table with input fields for editing... would be a 100% dynamic story to edit files with a database...

:)

Was This Post Helpful? 0
  • +
  • -

#7 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:22 AM

oh... just be really careful with allowing people to enter SQL statements in... you can lose your entire db that way ;)
Was This Post Helpful? 0
  • +
  • -

#8 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:25 AM

admin tool... normally placed on a nt-secured folder on your web app.... and only access for people who know what to do...

i think i'm pretty sure about what i do ;)

Was This Post Helpful? 0
  • +
  • -

#9 The Neoracle  Icon User is offline

  • Check, check, 1, 2.
  • member icon

Reputation: 21
  • View blog
  • Posts: 4,097
  • Joined: 30-March 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 08:27 AM

You do know that there are lots of one's that are already prebuilt?  Like PHPMyAdmin?  You could always download one of those and see how they do it?!?
Was This Post Helpful? 0
  • +
  • -

#10 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 10:29 AM

uhh... i don't like code of others, always a hard way to read...

like the snitz forum or the comersus e-shop...

just the sql statement would help me more than a whole finished product...

but it's a good idea, i'll have a look...

Was This Post Helpful? 0
  • +
  • -

#11 The Neoracle  Icon User is offline

  • Check, check, 1, 2.
  • member icon

Reputation: 21
  • View blog
  • Posts: 4,097
  • Joined: 30-March 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 01:21 PM

Ok, I got out the ol MySql book. I'm not sure if this is MySql specific but this is what I found:

SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE pattern]


I hope that helps, I put a sticky on the page if you need more info.

Peace,

Was This Post Helpful? 0
  • +
  • -

#12 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 13 December 2001 - 01:23 PM

thanks a lot :), i'll try tomorrow (localtime: 21:24) at work..

stay tuned ;)

Was This Post Helpful? 0
  • +
  • -

#13 Quentin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 26-November 01

Re: select/get column names of a specified table

Posted 15 December 2001 - 10:00 AM

sorry, this is mysql only...

found something nice in an e-book (*cough*)

select * from sysCOLUMNS

if you have the solution, you'd never believe you didn't see this table earlier :(

thanks anyway :)

Was This Post Helpful? 0
  • +
  • -

#14 praveen.chandran  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-February 08

Re: select/get column names of a specified table

Posted 25 February 2008 - 11:07 PM

View PostQuentin, on 12 Dec, 2001 - 02:48 AM, said:

hi there<p>i'm working on an sql server.<p>example: i got a table "user" with columns "usID","usNAME" and "usPWD"<p>i know it works, but i don't know the sql statement to SELECT all the column names of a specified table (here: "user")<p>SELECT * FROM sysobjects WHERE type = 'U' - with this statement i got every table in my database.. so i think it will work in a way like that..<p>do you have an idea?<p>thanks


           'Getting the full column names of the table in VB.NET
            Dim reader As SqlClient.SqlDataReader = Nothing
            Dim adapterTable As SqlClient.SqlDataAdapter = Nothing
            Dim columnname As String = Nothing
            Dim columnNames(100) As String
            Dim m as Integer

            adapterTable = New SqlClient.SqlDataAdapter("select top 1 * from " + tableName, con)
            reader = adapterTable.SelectCommand.ExecuteReader()
            While (reader.Read)
                For m = 1 To reader.FieldCount() - 2
                    columnname = columnname + reader.GetName(m).ToString + ","
                Next
            End While

            reader.Close()
            columnname = columnname.Substring(0, columnname.Length - 1)
            columnNames = columnname.Split(",") ' storing all the column names to the string array.


This post has been edited by PsychoCoder: 26 February 2008 - 12:46 PM

Was This Post Helpful? 0
  • +
  • -

#15 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: select/get column names of a specified table

Posted 25 February 2008 - 11:17 PM

praveen.chandran,

You just replied to a 7 year old thread. There is really no need to resurrect such old threads :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2