How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.