Generate column list in SQL Server

Question

I've cooked up the following SQL to generate the select portion of a query that, given a table, lists all columns in said table:

declare @cols nvarchar(max);
set @cols = '';

SELECT @cols += lower(', ' + c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('tablename')

select RIGHT(@cols, LEN(@cols) - 2);

-- output:
-- id, sex, isactive, displayname ...

Is there a better way to do this? Are there any tools that can perform this task quickly?

I'm trying to prevent having to write out a bunch of column names. Would much rather generate a list, as described in the code snippet, and remove columns I'm not going to need.

In SSMS, You can also right click on table and choose: Script Table As - Select to - New query editor window — Krishnraj Rana
– Krishnraj Rana, Commented Feb 19, 2014 at 5:09

Brandon J. Boone · Accepted Answer · 2014-02-19 01:50:55Z

4

In SQL Server Management Studio you can drag and drop the Columns folder into any Query window which gives you those same results quickly (though not programmatically of course).

enter image description here

answered Feb 19, 2014 at 1:50

Brandon J. Boone

16.5k4 gold badges78 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

culturalanomoly Over a year ago

+1 that's cool, but i don't like mice and the click-n-drag bit. looking for a more intelligent method. maybe a third-party tool, macro, or some other amalgamation.

Satheesh Variath · Accepted Answer · 2014-02-19 02:58:43Z

3

Another way

select stuff((SELECT  lower(' ,' + c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('<<TABLENAME>>')
for xml path('')),1,2,'')

answered Feb 19, 2014 at 2:58

Satheesh Variath

6804 silver badges10 bronze badges

Comments

KumarHarsh · Accepted Answer · 2014-02-19 02:53:22Z

0

select
@cols =stuff((SELECT  ', ' + c1.name FROM sys.columns c1 where c.object_id=c1.object_id for xml path('')),1,1,'')
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('tablename')

select @cols

answered Feb 19, 2014 at 2:53

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Collectives™ on Stack Overflow

Generate column list in SQL Server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related