pull the column names from a table(not data)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

pull the column names from a table(not data)

Author

Topic

ciaran
Starting Member

40 Posts

Posted - 2005-09-01 : 10:36:04

hi,
I have a table with 50 + columns in it. I need to add all the column names to a column in another table. Not the data just the column names. I dont want to script the table and then have to manipulate the text. I guess i can do an sp_help <tablename> and use excel to help me but there must be an easier way. any ideas

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-01 : 10:37:04

select name from syscolumns where id = object_id('mytbl')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 10:39:36

Do you want to create a new table having the same column names and data types of other table?

Then script it, rename table name and run it
Otherwise try this (although new table does not have indices, constraints,etc)

Select * into NewTable from OldTable where 1=2

Madhivanan

Failing to plan is Planning to fail

ciaran
Starting Member

40 Posts

Posted - 2005-09-01 : 10:40:06

perfect thanks..

quote:
Originally posted by nr

select name from syscolumns where id = object_id('mytbl')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 10:42:14

You can also use

Select column_name from information_Schema.columns where table_name='yourTable'

Madhivanan

Failing to plan is Planning to fail

ciaran
Starting Member

40 Posts

Posted - 2005-09-01 : 12:46:35

thanks for that.

quote:
Originally posted by madhivanan

You can also use

Select column_name from information_Schema.columns where table_name='yourTable'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources