column names seperated by comma

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 2005 Forums

Transact-SQL (2005)

column names seperated by comma

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 14:28:45

I have about 30 tables and each table has 50+ columns.

I need to generate 50+ select statement with all the column names in each table. It will be impossible to type in each column name. ( I know i can do select * from table). But I need the actual column names in my statement.

Is there a sql statement that I can run for each table with will give me the column names seperated by comma. for example,

tblSales
Id int
salesDate Datetime
salesPersonID int
saleAmount Double
saleDiscount double

I would like this column name to be like this after running the statment

id, salesdate, salespersonid, saleamount, salediscount etc...

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 14:36:19

And where would you like to put these lists?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 14:40:35

I am going to copy and paste this to create views.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-03 : 14:48:15

why not right click on table and then Script table as then SELECT to then New Query ..unless you are trying to do this dynamically?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 14:51:37

quote:
Originally posted by doran_doran

I am going to copy and paste this to create views.

To what end?

So the Users can't see the table names?

That's like an oooooooooooold DB2 trick

what are you going to name the views?

Are you just going to cut and paste them in SSMS?

I went down this road before I knew what you wanted


USE [master]
GO

DECLARE @str varchar(max)

SELECT @str = COALESCE(@str + ', ', '')+COLUMN_NAME 
  FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'spt_values'

SELECT @str

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 14:53:07

GREAT. I COULD DO THAT TOO. I am definitely not thinking today. Thanks Yosiasz.

However, following is a good one too...

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE
@colname sysname,
@cols varchar(1000)

SET @cols = ''

DECLARE c CURSOR FAST_FORWARD FOR
SELECT b.name
FROM sysobjects a
JOIN syscolumns b ON b.id = a.id
WHERE a.name = 'myLocalTableName'
AND a.type = 'U'
ORDER BY b.colorder

OPEN c
FETCH FROM c INTO @colname

WHILE @@Fetch_Status = 0
BEGIN
SET @cols = @cols + ', ' + @colname

FETCH FROM c INTO @colname
END

CLOSE c
DEALLOCATE c

SELECT cols = Substring(@cols, 3, 1000)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 14:55:13

Brett,

Your work too. Thanks everyone for the input.

USE [master]
GO

DECLARE @str varchar(max)

SELECT @str = COALESCE(@str + ', ', '')+COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'spt_values'

SELECT @str

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 15:02:47

I really Think you want this, then just cut and paste the DDL


SELECT SQL FROM (
SELECT ' CREATE VIEW AS v_' + TABLE_NAME + CHAR(13)+CHAR(10)+ ' AS ' AS SQL
, TABLE_NAME, 1 AS SQL_Group, 1 AS ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.Tables
UNION ALL
SELECT '		 SELECT ' + COLUMN_NAME AS SQL
, TABLE_NAME, 2 AS SQL_Group, 1 AS ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.Columns
 WHERE ORDINAL_POSITION = 1
UNION ALL
SELECT '		      , ' + COLUMN_NAME AS SQL
, TABLE_NAME, 2 AS SQL_Group, ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.Columns
 WHERE ORDINAL_POSITION <> 1
UNION ALL
SELECT '           FROM ' + TABLE_NAME AS SQL
, TABLE_NAME, 3 AS SQL_Group, 1 AS ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.Tables
UNION ALL
SELECT 'GO' AS SQL
, TABLE_NAME, 4 AS SQL_Group, 1 AS ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.Tables
) AS XXX
ORDER BY TABLE_NAME, SQL_Group, ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 15:16:13

WOW. Brett. THIS IS GOING TO SAVE ME ALL DAY WORK. I LIKE IT. THANKS...............

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-03 : 15:59:04

Brett,

I had to modify it (just a little). Here is the complete version. I took out the AS after CREATE VIEW.

SELECT SQL FROM (
SELECT ' CREATE VIEW view' + TABLE_NAME + CHAR(13)+CHAR(10)+ 'AS ' AS SQL
, TABLE_NAME, 1 AS SQL_Group, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Tables
UNION ALL
SELECT ' SELECT ' + COLUMN_NAME AS SQL
, TABLE_NAME, 2 AS SQL_Group, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Columns
WHERE ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME AS SQL
, TABLE_NAME, 2 AS SQL_Group, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Columns
WHERE ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME AS SQL
, TABLE_NAME, 3 AS SQL_Group, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Tables
UNION ALL
SELECT 'GO' AS SQL
, TABLE_NAME, 4 AS SQL_Group, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Tables
) AS XXX
ORDER BY TABLE_NAME, SQL_Group, ORDINAL_POSITION

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 16:05:58

I had it all nicely formatted....you still have (and NEED) the AS in there after the create

OK

Will you NOW tell us what value you are going to find this?

And I would put an underbar after the word view in the view name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources