DECLARE @TableName VARCHAR(255)
DECLARE @PrintTableName VARCHAR(255)
DECLARE @Template VARCHAR(MAX)

SET @TableName = 'table_name'
SET @PrintTableName = ''
SET @Template = '
** generate simple column list /v1.0
$table: {loop}$field, {/loop}
'

/*********************************************************************\
* TinySQL Code Generator {Version: 1.0}
*
* (C) 2007 Firoz Ansari
* http://www.firoz.name
* 
* Permission to use, copy, modify, and distribute this software for any
* purpose with or without fee is hereby granted, provided that the above
* copyright notice and this permission notice appear in all copies.
*
* THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
* WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
* MERCHANTABILITY AND FITNESS.
*
* Tag Hint:
* $table    : Table name
* $field    : Column Name
* $type     : .NET Data Type
* $sp_type  : SQL Data Type
* $default  : .NET Default Value
* $length   : Column Max Length
*
* {loop}    : Start Loop
* {/loop}   : End Loop
\*********************************************************************/
SET NOCOUNT ON
DECLARE @ColumnName VARCHAR(255)
DECLARE @DataType VARCHAR(255)
DECLARE @MaxLength INT
DECLARE @IsNull INT
DECLARE @RType VARCHAR(100)
DECLARE @RDefault VARCHAR(100)
DECLARE @Snippet VARCHAR(MAX)
DECLARE @TempSnippet VARCHAR(MAX)
DECLARE @RenderedSnippet VARCHAR(MAX)

DECLARE @CurrentPos INT
DECLARE @StartTag VARCHAR(10)
DECLARE @EndTag VARCHAR(10)
DECLARE @StartPos INT
DECLARE @EndPos INT

-- pull column information of selected table
DECLARE CurColumnList CURSOR FOR
SELECT [Column].Name AS [ColumnName], systypes.Name AS [DateType], [Column].max_length as [MaxLength], [Column].is_nullable as [IsNull]
FROM Sys.Columns [Column]
INNER JOIN SysTypes ON [Column].system_type_id = SysTypes.xtype
LEFT JOIN SysComments ON [Column].default_object_id = SysComments.id
LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = [Column].object_id AND ex.minor_id = [Column].column_id AND ex.name = 'MS_Description'
WHERE OBJECTPROPERTY([Column].object_id, 'IsMsShipped')=0 AND OBJECT_NAME([Column].object_id) = @TableName
ORDER BY OBJECT_NAME([Column].object_id), [Column].column_id

SET @StartTag = '{loop}'
SET @EndTag = '{/loop}'

-- find first start-loop tag in template
SET @StartPos = CharIndex(@StartTag, @Template, 0)
SET @CurrentPos = 1
WHILE @CurrentPos > 0 AND @StartPos > 0
BEGIN
	-- goto the end of start-loop tag string
	SET @StartPos = @StartPos+Len(@StartTag)
	SET @EndPos = CharIndex(@EndTag, @Template, @StartPos)
	SET @Snippet = SubString(@Template, @StartPos, @EndPos-@StartPos)

	SET @TempSnippet = ''
	OPEN CurColumnList
	FETCH NEXT FROM CurColumnList INTO @ColumnName, @DataType, @MaxLength, @IsNull
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @RenderedSnippet = @Snippet
		SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$table', @PrintTableName)
		SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$field', @ColumnName)
		SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$length', @MaxLength)

		-- set .net data type for respective data type of column
		SELECT @RType = CASE @DataType
			WHEN 'bit' THEN 'Boolean'
			WHEN 'tinyint' THEN 'Int16'
			WHEN 'smallint' THEN 'Int32'
			WHEN 'bigint' THEN 'Int64'
			WHEN 'int' THEN 'Int32'
			WHEN 'varchar' THEN 'String'
			WHEN 'nvarchar' THEN 'String'
			WHEN 'char' THEN 'String'
			WHEN 'text' THEN 'String'
			WHEN 'float' THEN 'Double'
			WHEN 'decimal' THEN 'Double'
			WHEN 'money' THEN 'Double'
			WHEN 'smalldatetime' THEN 'DateTime'
			WHEN 'datetime' THEN 'DateTime'
			WHEN 'uniqueidentifier' THEN 'Guid'
			ELSE '?'+@DataType
		END

		IF (UPPER(@DataType) != 'VARCHAR')
			SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$sp_type', UPPER(@DataType))
		ELSE
			SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$sp_type', UPPER(@DataType) + '(' + CAST(@MaxLength AS VARCHAR(10)) + ')')

		SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$type', @RType)

		-- set .net default value for respective data type of column
		SELECT @RDefault = CASE @DataType
			WHEN 'bigint' THEN '0'
			WHEN 'int' THEN '0'
			WHEN 'tinyint' THEN '0'
			WHEN 'smallint' THEN '0'
			WHEN 'bit' THEN '0'
			WHEN 'varchar' THEN 'String.Empty'
			WHEN 'nvarchar' THEN 'String.Empty'
			WHEN 'char' THEN 'String.Empty'
			WHEN 'text' THEN 'String.Empty'
			WHEN 'float' THEN '0.0'
			WHEN 'decimal' THEN '0.0'
			WHEN 'money' THEN '0.0'
			WHEN 'smalldatetime' THEN 'DateTime.MinTime'
			WHEN 'datetime' THEN 'DateTime.MinTime'
			WHEN 'uniqueidentifier' THEN 'Guid.Empty'
			ELSE '?'+@DataType
		END
		SET @RenderedSnippet = REPLACE(@RenderedSnippet, '$default', @RDefault)
		SET @TempSnippet = @TempSnippet + @RenderedSnippet
		FETCH NEXT FROM CurColumnList INTO @ColumnName, @DataType, @MaxLength, @IsNull
	END
	CLOSE CurColumnList

	-- put back rendered snippet into template
	SET @Template = REPLACE(@Template, @StartTag+@Snippet+@EndTag, @TempSnippet)

	-- get next position for start loop tag in template
	Set @StartPos = CharIndex(@StartTag, @Template, @EndPos+1)
	SET @CurrentPos = @StartPos
END

-- if printable table name is not specified than use table name instead
IF (@PrintTableName = '')
	SET @PrintTableName = @TableName

-- replace $table tag to printable table name
SET @Template = REPLACE(@Template, '$table', @PrintTableName)

--ignore first line of template, which can be used as template description, author, template version, additional comment etc.
--simply remove this line if you dont want comment segment in template
SET @CurrentPos = CharIndex(CHAR(13)+CHAR(10), @Template, 0)
SET @CurrentPos = CharIndex(CHAR(13)+CHAR(10), @Template, @CurrentPos+2) + 2
SET @Template = SubString(@Template, @CurrentPos, Len(@Template))
--/

--print rendered code in result pane
PRINT @Template

DEALLOCATE CurColumnList
SET NOCOUNT OFF