Tip: Get list of columns for the table

When you dealing with dynamic SQL or unknown table structure, SQL meta data could help recover the missing information.

There are two main system tables which give you such information:

  • dbo.sysobjects
  • dbo.syscolumns

Example below shows how to gather column list for specified table.

DECLARE
  @TABLENAME varchar(256),
  @COLUMNS varchar(4000)

SET @TABLENAME = 'mytable'
SET @COLUMNS = ''

SELECT @COLUMNS = @COLUMNS + c.name + ', '
FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @TABLENAME AND o.xtype='U'
ORDER BY colid

SET @COLUMNS = SUBSTRING(@COLUMNS, 1, Datalength(@COLUMNS) - 2)

SELECT @COLUMNS

or if you prefer more generic approach

DECLARE
  @TABLENAME varchar(256),
  @COLUMNS varchar(4000)

SET @TABLENAME = 'mytable'
SET @COLUMNS = ''

SET @COLUMNS = ''
SELECT @COLUMNS = @COLUMNS + COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TABLENAME
ORDER BY ORDINAL_POSITION

SET @COLUMNS = SUBSTRING(@COLUMNS, 1, Datalength(@COLUMNS) - 2)
SELECT @COLUMNS

Result is a coma-separated list of columns in the table in order they appear.

Note. Check for xtype in the first example could be useful but not required, since you probably do not mix table name with any other object names. Otherwise, it would be necessary to ensure that you are looking at the table and not something else.
Other possible values for this column are:

  • S – System tables
  • U – User table
  • TR – Triggers
  • P – Stored procedure
  • RF – Replication filter stored procedure
  • X – Extended stored procedure
  • V – View
  • TF – Functions
  • C – CHECK constraint
  • D – DEFAULT constraint
  • F – FOREIGN KEY constraint
  • PK – PRIMARY KEY constraint (type is K)
  • UQ – UNIQUE constraint (type is K)
  • L – Log
This entry was posted in Fun stuff with SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

  • About

    From a junior developer through Sr Manager position, I was always interested in new technologies. Passionate speaker, IT junky, developer, architect, team lead, and development manager - many hats, one goal - making software better and being closer to people’s needs. For the most part I am using my blog as a scratch pad, writing small articles on things which I came across, was asked about more then once, and which would otherwise require additional research again and again.

       
  • Testimonials

    I’ve used your Greydragon theme ever since I switched to using Gallery3. It looks very slick; it does everything that I want; it is easy to customise the appearance with its many options; image preloading makes it faster than other … Continue reading

    Brian Stewart

    The greydragon theme is fantastic. It’s clean, stable and feature rich. It took me a while to decide to move to G3 but with a there like this one, I could no longer resist. Thanks!

    Ed
  • Categories

%d bloggers like this: