getting column names from a table 
Author Message
 getting column names from a table
Is there an SQL standard way to find out the colum names and datatypes in a
table with a query of some sort?

thanks,
Vince



Mon, 10 Mar 2003 03:00:00 GMT
 getting column names from a table

sp_columns '<tablename>' might do what you need.

You can also try selecting from the system tables, but that way isn't
the best since the system tables can change between versions.

A third method (and perhaps the "most standard") would be to use the
new INFORMATION_SCHEMA views. There is a view called COLUMNS that would
be of use to you. Something like:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG = '<database name>'
  and TABLE_NAME = '<table name>'

There are additional columns available to you, which are explained in
the Transact SQL help.

   Good luck,
     -Tom.



Quote:
> Is there an SQL standard way to find out the colum names and
datatypes in a
> table with a query of some sort?

> thanks,
> Vince

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 10 Mar 2003 03:00:00 GMT
 getting column names from a table
thanks everyone for the useful information!

Vince


Quote:
> Is there an SQL standard way to find out the colum names and datatypes in
a
> table with a query of some sort?

> thanks,
> Vince



Wed, 12 Mar 2003 03:00:00 GMT
 getting column names from a table
This will get you column names and data types, can be modified to use it in
other ddl.
Replace <TABLE> with a table name.

-- PROCEDURE DECLARATION --

 + CASE  WHEN C.NAME IN ( 'int' , 'datetime' ) THEN ' '
  ELSE ' ( '  + CONVERT ( VARCHAR , A.PREC ) + ' ) '
 END
FROM SYSCOLUMNS A (NOLOCK) , SYSOBJECTS B (NOLOCK) , SYSTYPES C (NOLOCK)
WHERE A.ID = B.ID AND A.XTYPE=C.XTYPE
AND B.NAME = '<TABLE>'
ORDER BY A.NAME


Quote:
> Is there an SQL standard way to find out the colum names and datatypes in
a
> table with a query of some sort?

> thanks,
> Vince



Fri, 14 Mar 2003 11:48:56 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Getting Column Names From Access Table

2. Getting table and column names through Visual Basic.

3. getting column names for tables..

4. Getting type name of many column names

5. rdo, getting the column name and the column type

6. Getting the Column Name of the Primary Key Column

7. rdo, getting the column name and the column type

8. Getting table names without actually knowing their names...

9. Getting rid of the IDENTITY property on a column withou getting rid of the column

10. HHHHelp (Table Names and Column Names)

11. Result Column Names to include table name!

12. table names and column names w/ type?


 
Powered by phpBB® Forum Software