Extracting Column Names for a Table in SQL as a CSV String

21 Comments

• Thanks, This was very helpfull
  

• Awesome! Very useful - that ones going straight to my "snippets", thanks!
  

• hi this is very usefull to me....i enjoyed a lot.
  

• Just what I need, Thanks
  

• Thanks very much, but I'm wondering how to get every column in a seperate row or column space without building up a string. Is this possible?
  

• I have an alternate solution that you can add onto easily enough.
  
  I wanted to dynamically create the data contract objects I was using so that whenever I built my solution in visual studio, a build step would execute this sql statement and use the dataSet to dynamically write the dataContract classes.
  
  Here's the stored proedure I wrote for it:
  
  create procedure GetAllTableColumns
  AS
  -- select all user tables
  declare @select_string nvarchar (4000)
  declare @table_name varchar(50)
  declare table_cursor Cursor
  FOR select name from sys.objects where type='U'
  set @select_string = 'select name from sys.objects where type=''U'' '
  open table_cursor
  fetch next from table_cursor into @table_name
  while (@@fetch_status = 0)
  begin
  set @select_string = @select_string + N'select top 1 * from ' + @table_name + ' '
  fetch next from table_cursor into @table_name
  end
  close table_cursor
  deallocate table_cursor
  
  exec sp_executesql @select_string
  
  return
  
  The first table returned is a list of all the table names (the names of the classes i'm going to write) then each other table has all the column names (accessable through the DataTable.Columns[index].ColumnName property).
  
  Your idea is a little nicer, putting the data into rows, plus mine can return junk data in the rows too.
  
  How would you combine these?
  

• NICE!
  
  i did not want to use cursors i got one more way
  
  DECLARE @c varchar(4000), @t varchar(128)
  
  SET @c = ''
  
  SET @t='clusterattributedetails'
  
  SELECT @c = @c + c.name + ','
  
  FROM syscolumns c where [id] = OBJECT_ID (@t)
  
  ORDER BY colid
  
  SELECT Substring(@c, 1, Datalength(@c) - 1)
  
  very much the same just that i access only syscolumns!!
  
  

• CREATE procedure [dbo].[RetSp_ColumnList]
  
  (
  
  @tablename varchar(128),
  
  @columnlist varchar(4000) output
  
  )
  
  AS
  
  BEGIN
  
  /* *****************************************************************************
  
  Procedure           : RetSp_ColumnList
  
  Function / Purpose       : To gives column list of a particular table
  
  Trigger       :
  
  Package Name :
  
  Source File       :
  
  Name of Author(s)   : Shivam Yadav
  
  Phase No :
  
  Start Date       : 30/10/2007
  
  Called By Dataset : GRD_ASSORTMENT_CANDIDATES_DIRECTINPUT
  
  Called By Business Process : BP_COLUMN_LIST
  
  Calls       :
  
  Tables involved     :
  
  Table Name :
  
  Maintenance History :
  
  #No. Modified By Modification Date   Purpose
  
  *****************************************************************************
  
  */
  
  SET NOCOUNT ON
  
  SET @columnlist = ''
  
  --SET @tablename='clusterattributedetails'
  
  SELECT @columnlist = @columnlist  + c.name + ','
  
  FROM syscolumns c where [id] = OBJECT_ID (@tablename)
  
  ORDER BY colid
  
  /* declare @list as varchar(4000)
  
  exec [dbo].[RetSp_ColumnList] 'clusterattributedetails', @list output
  
  print @list
  
  */
  
  Select @columnlist as COLUMNSLIST
  
  END
  
  drop procedure [dbo].[RetSp_ColumnList]
  
  

• --This will return a table of all your column names for a database
  
  declare @t table (tbl varchar(50), col varchar(50))
  declare @table_name varchar(50)
  declare table_cursor Cursor
  
  FOR select name from sys.objects where type='U'
  open table_cursor
  fetch next from table_cursor into @table_name
  
  while (@@fetch_status = 0)
  begin
  insert into @t
  SELECT @table_name,name
  FROM syscolumns
  WHERE id = (SELECT id FROM sysobjects WHERE name=@table_name)
  fetch next from table_cursor into @table_name
  end
  
  close table_cursor
  deallocate table_cursor
  
  select * from @t
  

• iclemartin !
  really elegant code :) without resorting to cursors.
  

• --This allows for schemas and same table names within different schemas
  
  declare @t table (schemaId int, tbl varchar(50), col varchar(50))
  
  declare @table_name varchar(50)
  declare @objectId int
  declare @schemaId int
  
  declare table_cursor Cursor
  
  FOR select name,object_id,schema_id from sys.objects where type='U'
  
  open table_cursor
  
  fetch next from table_cursor into @table_name,@objectId,@schemaId
  while (@@fetch_status = 0)
  
  begin
  
  insert into @t
  SELECT @schemaId as schemaId,@table_name,name
  FROM syscolumns
  WHERE id = @objectId
  fetch next from table_cursor into @table_name,@objectId,@schemaId
  
  end
  
  close table_cursor
  
  deallocate table_cursor
  
  
  select ss.name as schemaName, t.tbl, t.col from @t t inner join sys.schemas ss on t.schemaId = ss.schema_id
  

• DECLARE @t TABLE (schemaName varchar(255), tableName varchar(255), colName varchar(255))
  
  INSERT INTO @t
  
  SELECT
  
  S.[name],
  
  T.[name],
  
  C.[name]
  
  FROM
  
  sys.objects T
  
  JOIN sys.schemas S
  
  ON S.schema_id = T.schema_id
  
  JOIN sys.columns C
  
  ON C.[object_id] = T.[object_id]
  
  WHERE
  
     T.type='U'
  
  SELECT * FROM @t
  
  

• Newbie here and I really want to learn.
  
  I need to extract all the TABLE names along with their column names in sql as a CSV file.
  
  From that file, I wanted to use this information to create a webservice (that I would have to create or find in google), that would probe each table for the user ID so that I could display to said user all information connected to them in a dataview or datagrid or table in VB.Net
  
  Could someone help me with this?
  

• The same can be done using information schema for SQL 2000
  
  declare @StrColSql varchar(2000)
  declare @StrSql nvarchar(2000)
  declare @TableName varchar(200)
  declare @ColmNames varchar(800)
  set @TableName = 'Phases'
  set @StrColSql = ''
  select @StrColSql = @StrColSql + column_name + ',' from information_schema.columns where table_name = @TableName
  SET @ColmNames = Substring(@StrColSql, 1, Datalength(@StrColSql)-1)
  select Datalength(@StrColSql)
  print @ColmNames
  
  visit www.codepal.co.in
  

• Hi Wayne Allen
  
  I am santosh from INDIA
  
   Thanks For the Qurty Which u have posted It's really USe full For All
  
  Cheers
  
  san
  
  

• Lovely, saved me boatloads of time. Thanks dude.
  

• you can get all the columns of a table by following query..
  
  select COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'table_name'
  

• Thanks Vicky. At the time of the original post the information_* tables didn't exist.
  

• thanks...very helpful post
  

• Thanks. Exactly what I was looking for.
  

• Thanks very useful keep on posting