How do I get the columns names? 1

[Guest_imported]

Now that I have the names of the tables, how do I get the names of the table's columns?

 

[ColinM]

One way to do it is:

select column_name from information_schema.columns
where table_name = 'yourtable'

 

[rajeevnandanmishra]

Hi Erric,

I had written the solution for you in your last question. May be you had not followed up that.
Here is the solution for getting table name and their column names from SQL server.

select tables=a.name, columns=b.name from sysobjects a join syscolumns b on a.id=b.id WHERE a.type='U'

And if you want more details about columns i.e. data type, length, precision e.t.c, you can select reruired columns from syscolumns in above query.

Hope this will help you.

 

[StevenK]

I use the syntax :
SELECT
(CAST([Name] AS CHAR)) ColName,
(CAST([ColOrder] AS INT)) ColOrder
FROM [syscolumns]
WHERE ID = OBJECT_ID(N''[dbo].[Table-Name]
ORDER BY [ColOrder]
which produces a list of columns and their respective ordering in the indicated table [Table-Name].

Regards,
Steve

 

[IonelBurtan]

1. I think the the most elegant and fast way is to use built in SPs. You should use sp_columns, to retrieve the column names.

2. If you want the column names of a table in Query Analizer you should use
SET FMTONLY ON
SELECT TOP 1 FROM yourtable
SET FMTONLY OFF

Hope this helps, s-) Blessed is he who in the name of justice and good will, shepards the week through the valley of darknees...

http://home.domaindlx.com/ionelb

 

[NathanGriffiths]

I agree with the above, stored procedures are the way to go. Here's an example, it takes a tablename as a parameter and returns the column names in order with data type and other useful information. The results also fit nicely on landscape A4 paper! Obviously, SQL Server 2000's QA Object Browser makes this somewhat redundant, but its handy if, like me, you still use SQL Server 7.

cheers,

Nathan


CREATE PROCEDURE sp_TableStructures (@prm_tablename varchar(50)) AS

select left(o.name, 30) as

,
',' + left(c.name,30) as field,
',' + left(t.name,20) as type,
',',.
c.length,
',',
c.xprec as prec,
',',
c.xscale as scale
from sysobjects o,
syscolumns c,
systypes t
where o.type = 'U'
and o.id = c.id
and t.xtype = c.xtype
and o.name = @prm_tablename
order by c.colorder