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
