SQL- Stored procedure: list out all columns of a table

Sometimes, you need to create a temp table or whatever, you need to list out all the columns. If just only 2 or 3 columns sure that won't bother you, but if the table has 100 columns, its gonna kill you if you have to type everyone of it. So, here is the stored procedure for you. It will list out all columns of a table in one line and separate the columns with comma.


Usage: exec SP_ColName 'TABLENAME'

===
IF EXISTS (SELECT * FROM [sysobjects] WHERE [id] = OBJECT_ID(N'[dbo].[SP_ColName]'))
DROP PROCEDURE [SP_ColName]
GO
-- Usage: SP_ColName
-- It prints out the column names of the given table in one line delimited by ', '
CREATE PROCEDURE [SP_ColName] @TableName sysname
AS
BEGIN
DECLARE @ColumnList nvarchar(max)
DECLARE @ColumnName nvarchar(max)
SET @ColumnList = ''
DECLARE cur CURSOR FOR
SELECT [name] FROM [syscolumns] WHERE [id] = OBJECT_ID(@TableName) ORDER BY [colid]
OPEN cur
DECLARE @OutStr varchar(max)
SELECT @OutStr = ''
PRINT UPPER(@TableName)
FETCH NEXT FROM cur INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @OutStr = @OutStr + @ColumnName + ', '
FETCH NEXT FROM cur INTO @ColumnName
END
IF LEN(@OutStr) >= 2 PRINT LEFT(@OutStr,LEN(@OutStr)-2)
CLOSE cur
DEALLOCATE cur
END
GO