Creating a comma separated list of all columns in a table

Category : Scripts User Rating : 5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




This script creates a comma separated list of all columns in a table. This can be useful for creating SQL INSERT and SELECT statements etc. Set the table name and schema name in the ‘User Customisation’ section at the top of the script.

Schema Names

The original version of this script (prior to April 2013) only worked for tables in the 'dbo' schema, but this version now works with any schema. The schema name in the 'User Customisation' section should normally be left at 'dbo' but can be set to another schema if your database contains other schemas.

--*********************************************************************
-- Description  : Creates a comma separated list of all columns in a
--                table, adding square brackets if a column
--                name contains spaces.
-- Author/Owner : © SQL Matters 2011-13
--*********************************************************************
 
DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)
 
-----------------------------------------------------------------------
-- *** User Customisation
 
-- Set up the name of the table here :
SET @TABLE_NAME = 'Address'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'
 
-----------------------------------------------------------------------
 
DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)

IF @SCHEMA_NAME =''
  BEGIN
  PRINT 'Error : No schema defined!'
  RETURN
  END
 
IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
          ON T.schema_id=S.schema_id
          WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
  BEGIN
  PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
        @SCHEMA_NAME+''' does not exist in this database!'
  RETURN
  END
 
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT   CASE WHEN PATINDEX('% %',C.name) > 0
         THEN '['+ C.name +']'
         ELSE C.name
         END
FROM     sys.columns C
JOIN     sys.tables T
ON       C.object_id  = T.object_id
JOIN     sys.schemas S
ON       S.schema_id  = T.schema_id
WHERE    T.name    = @TABLE_NAME
AND      S.name    = @SCHEMA_NAME
ORDER BY column_id


SET @vvc_ColumnList=''
 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

WHILE @@FETCH_STATUS=0
  BEGIN
  SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName

  -- get the details of the next column
  FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

  -- add a comma if we are not at the end of the row
  IF @@FETCH_STATUS=0
    SET @vvc_ColumnList = @vvc_ColumnList + ','
  END

CLOSE TableCursor
DEALLOCATE TableCursor

PRINT 'Here is the comma separated list of column names :'
PRINT '--------------------------------------------------'
PRINT @vvc_ColumnList
By way of example, here is the result of above query when run against the AdventureWorks2008 sample database : Comma separated column list
Link back to this article : http://www.sqlmatters.com/Articles/Creating a comma separated list of all columns in a table.aspx

Keywords

Scripts,CSV


Comments
Post by Easwaran on Fri 05 Apr 2013 07:19. Report Inappropriate Post

If the table name is given with schema name it is saying that table does not exist. Eg., [MySchema.Employee].tEmployee
Post by SQL Matters on Tue 09 Apr 2013 19:16. Report Inappropriate Post

Many thanks for your comments and interest in the article. The original script was indeed just for the dbo schema, however I've just updated the script so it will work with other schemas. You just need to define the schema name along with the table name in the customisation section at the top of the script.
Hope it is of use.
Post by pandotra on Sat 29 Jul 2017 19:53. Report Inappropriate Post

I made a similar script but realized that SSMS gives you the list in Right-click on table> Select Top 1000 rows:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [busn_dt_id]
,[opt_lck_ctl]
,[rcd_crt_nm]
,[rcd_crt_ts]
,[busn_dt]
FROM [nvpers_2pd].[dbo].[be_busn_dt]
Post by Ranjith on Mon 11 Dec 2017 09:46. Report Inappropriate Post

Nice...But i need column name and column values...help me

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered