how-to generate list of column names of a table in sql server 2005 2008

USE [GenApp]

GO

/****** Object:  StoredProcedure [dbo].[procUtils_PrintTableCols]    Script Date: 09/11/2009 14:25:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[procUtils_PrintTableCols]

@TableName [varchar](50)

WITH EXECUTE AS CALLER

AS

BEGIN -- proc start                                  

SET NOCOUNT ON;                                  

BEGIN TRY        --begin try                        

--CODE SNIPPET TO LIST TABLE COLUMNS       

-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE      

--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name       

--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'      

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'          

--Declare the Table variable           

DECLARE @ColNames TABLE          

(          

Number INT IDENTITY(1,1), --Auto incrementing Identity column          

ColName VARCHAR(300) , --The string value          

DataType varchar(50) --the datatype 

)          

--Decalre a variable to remember the position of the current delimiter          

DECLARE @CurrentDelimiterPositionVar INT           

--Decalre a variable to remember the number of rows in the table          

DECLARE @Count INT          

--Populate the TABLE variable using some logic          

INSERT INTO @ColNames SELECT column_name , Data_type from INFORMATION_SCHEMA.COLUMNS       

where TABLE_NAME=@TableName          

--Initialize the looper variable          

SET @CurrentDelimiterPositionVar = 1          

--Determine the number of rows in the Table          

SELECT @Count=max(Number) from @ColNames          

--A variable to hold the currently selected value from the table          

DECLARE @ColumnName varchar(300);          

DECLARE @DataType varchar(50)

--Loop through until all row processing is done          

WHILE @CurrentDelimiterPositionVar <= @Count          

BEGIN          

--Load current value from the Table          

SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar

SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar  

--Process the current value          

if @CurrentDelimiterPositionVar = @Count          

print '[' + @ColumnName + '] --type of ' + @DataType -- this is the last row no comma!      

else       

print '[' + @ColumnName + '], --type of '  + @DataType -- print it without the ,      

-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName      

-- print @ColumnName    --SIMPLE PRINT      

--Increment loop counter          

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;          

END          

PRINT 'Select top 5  * from '+ @TableName +' order by 1 desc   '

END TRY        --end try                        

BEGIN CATCH                              

print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +                     

'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +       

CAST(ERROR_SEVERITY() AS varchar(10)) +                     

'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +       

'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                              

END CATCH                              

END --procedure end                                   

/*       

USE [GenApp]      

GO      

SELECT NAME FROM SYS.tables where name like '%feature%'      

EXEC    [dbo].[procUtils_PrintTableCols] @TableName = N'fEATURE'

GO      

*/