Sign in to follow this  
kag1

[web] SQL + Column Names

Recommended Posts

Hello, i'm at college and can't connect to a MS SQL Server from here with my front end programs I have. (I think the ports are blocked maybe)..but I can connect from a webserver I have with the use of ASP..so I was kinda thinkin of maybe making my own little frontend. I have been looking through SQL tutorial sites and such, but can't find a way to return the column names of a specific table...or find out what tables are in the database without already knowing the table name.. Can anyone help me out?

Share this post


Link to post
Share on other sites
in oracle you can use:

table names:
select table_name from user_tables;

column names:
select column_name from user_tab_columns whre table_name = 'MYTABLE'


Lazzar

Share this post


Link to post
Share on other sites
There are two ways.

One is by reading from the system (sys*) tables. For example this will return a list of all tables in the database:

SELECT name FROM sysobjects WHERE xtype='U'

I suggest you lookup the documentation on MSDN (or buy a copy SQL Server to get the big poster) for more information (you can read everything about the database from the sys* tables, including tables, columns, indexes, contraints, etc).

The other way (probably easier if you are using ASP) is by using an ADOX catalog. For example to do what we did above (note that this is actual ASP script, using VBScript as the language):

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "blah"
Set Catalog = Server.CreateObject("ADOX.Catalog")
Catalog.ActiveConnection = Connection
For Each Table in Catalog.Tables
Response.Write Table.Name & "<br>"
Next


As before, you can get all the information you want, just read the properties (lookup the reference on MSDN for all the properties)

Share this post


Link to post
Share on other sites
Also: sp_help will show all objects in the database. Supply it an object name (eg: MyTable) and it'll show a bunch of info about it, including the column specs... But Michalson's way is probably the most practical.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this