List all column's name - SQL Server Forums

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

SQL Server Development (2000)

List all column's name

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-03-09 : 14:49:05

Is there any way (string builder) to list all columns' name? I have one table and need to be listed about 20 column's name. I need to change sentence from

select * from myTable

to

select columnA, columnB..., columnZ from myTable.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-09 : 14:56:23

Have Query Analyzer do it. Right click on table in the object browser, Script Object to New Window As Select

activecrypt
Posting Yak Master

165 Posts

Posted - 2006-03-09 : 21:53:29

Hi,
or you may run like this from query analyzer :
select top 0 * from yourtable

Andy Davis
Sql Shield Team
--------------------------------------------
SQL Server Encryption Software
http://www.sql-shield.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 01:00:38

Select * from yourTable where 1=2

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-10 : 08:39:46

Here's a SQL way to do it:

SELECT 'SELECT ' + CASE WHEN ORDINAL_POSITION=1 THEN '' ELSE ', ' END + QuoteName(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'

The rowset that is generated will contain the SELECT clause with each column name specified. You'll need to add the FROM clause and any others you may need (WHERE, GROUP BY, etc.)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-10 : 08:48:04

Should be like this ?

SELECT 	CASE WHEN ORDINAL_POSITION=1 THEN 'SELECT ' ELSE ', ' END + QuoteName(COLUMN_NAME)
FROM 	INFORMATION_SCHEMA.COLUMNS
WHERE 	TABLE_NAME = 'myTable'

sample result : 
SELECT [col1]
, [col2]
, [col3]

----------------------------------
'KH'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-10 : 19:14:02

Yes, you are right. I hadn't yet had my coffee.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-03-10 : 19:43:51

Many thank to all of you! Finally, I used Khtan's way to test, it works great!
I used authors table from pub, the result like bellow. Is it possible make one line?

SELECT [au_id]
,[au_lname]
, [au_fname]
, [phone]
, [address]
, [city]
, [state]
, [zip]
, [contract]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-10 : 20:05:04

[code]DECLARE @sql VARCHAR(1024)
SELECT @sql = isnull(@sql, '') +
CASE WHEN ORDINAL_POSITION=1 THEN
'SELECT '
ELSE ', '
END
+ QuoteName(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'authors'

--PRINT @sql
SELECT @sql[/code]

----------------------------------
'KH'

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-03-10 : 20:38:46

Thank you! I tested it, it works as I need! I'll save it as a utility to create store procedure! It saves a lot of time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources