retrieve column name

[RP]

I want to retrieve column names (Exact field names) of a table. How to
do that?

 

[Mythran]

I want to retrieve column names (Exact field names) of a table. How to
do that?

Depends on the DBMS (if those are the "column"s and "table"s you are
referring to). If SQL Server 2000, you can call the FillSchema method or
execute the sp_columns stored procedure on the server itself.

HTH,
Mythran

 

[Nicholas Paldino [.NET/C# MVP]]

RP,

That depends completely on the underlying data source. For SQL Server
2000 and below, you can use the syscolumns table. For SQL Server 2005,
sys.columns is recommended. If you are using another database system, then
that will have its own way of retreiving this info.

You can create a connection and then call the GetSchema method on it.
However, the way that the schemas are defined and what they return are
provider-specific, so you will need to have some knowledge beforehand.

 

[RP]

I am using SQL Server 2005. Can it be retrieved using SQL?

 

[Nicholas Paldino [.NET/C# MVP]]

RP,

Yes, as Mythran stated, you can execute the sp_columns stored procedure,
or you can perform a select against sys.columns (where the object_id is the
id of the table you want to get the columns for).

 

[RP]

How to know the object id of my table?

 

[Nicholas Paldino [.NET/C# MVP]]

RP,

You might want to take a look at the object_id function in the SQL
Server Books Online documentation.

 

[zacks]

I am using SQL Server 2005. Can it be retrieved using SQL?

The "safest" way to get metatdata from SQL Server like table list and
column lists is to use one of the INFORMATION_SCHEMA Views. For a list
of tables in the current database:

select * from information_schema.tables

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

The "safest" way to get metatdata from SQL Server like table list and
column lists is to use one of the INFORMATION_SCHEMA Views. For a list
of tables in the current database:

select * from information_schema.tables

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='xxxx'

to be more specifically.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

That depends completely on the underlying data source. For SQL Server
2000 and below, you can use the syscolumns table. For SQL Server 2005,
sys.columns is recommended. If you are using another database system, then
that will have its own way of retreiving this info.

You can create a connection and then call the GetSchema method on it.
However, the way that the schemas are defined and what they return are
provider-specific, so you will need to have some knowledge beforehand.

Using INFORMATION_SCHEMA's should be the recommended way.

Seems rather silly to use a database specific function to
do something that is standardized.

Arne