SQLUSA

Microsoft SQL Server 2005 Best Practices

How to get the columns in a table?

One way of getting them is to look in the system INFORMATION_SCHEMA view "columns". Here is an example which generates the column list of a table in comma separated format.

 

USE AdventureWorks

DECLARE @Schema varchar(64), @Table varchar(64), @Columns varchar(2000)

SET @Table = 'Contact'
SET @Schema = 'Person'

SELECT @Columns = COALESCE(@Columns + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @Table
and table_schema=@Schema
ORDER BY ordinal_position

SELECT @Columns


 

SQLUSA - The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page