Dynamic and Unique field Select creator

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************************************
** Name: _Select
** Desc: creates a select statement which specifies all columns for a table
**       doesn't have all the [] around the columns that the native SQL create does
**		 plus provides unique titles for all the fields  
** Parm: Table name and an alias 
** Retr: select with all the columns from tablename followed by alias
** Auth: D Simmons
**
** Mod Date:
** 05.15.07 - dts Original version
*********************************************************************************************************/

CREATE PROCEDURE [dbo].[_Select] (
	@Table		VARCHAR(100),
	@Alias		VARCHAR(20)
)

AS

 SET NOCOUNT ON

	-- ------------------------------------------------------------------------
	-- DECLARATION and TABLE CREATION
	-- ------------------------------------------------------------------------
	DECLARE	@CurrOrd	INT,
			@SQL		VARCHAR(4000),
			@CurrCol	VARCHAR(200),
			@LF			CHAR(1),
			@TAB		CHAR(1),
			@SQ			CHAR(1),
			@ColumnName VARCHAR(200)

	-- ------------------------------------------------------------------------
	-- INITIALIZE
	-- ------------------------------------------------------------------------
	SET @SQL = ''
	SET @LF = CHAR(10)
	SET @TAB = CHAR(9)
	SET @SQ = CHAR(39)

	-- ------------------------------------------------------------------------
	-- LOGIC
	-- ------------------------------------------------------------------------

	-- get the first column for this table
	SET @CurrOrd =	(	SELECT	MIN(Ordinal_Position)
						 FROM	INFORMATION_SCHEMA.COLUMNS
						 WHERE	Table_Name = @Table 
					) 

	-- if it is NULL the table name is bad
	IF @CurrOrd IS NULL BEGIN
		PRINT 'Table name ' + @Table + ' does not exist!'
		RETURN		
	END
	

	-- while we have a column
	WHILE @CurrOrd IS NOT NULL BEGIN

		-- get the current column for this ordinal position
		SET @CurrCol = ( SELECT Column_Name 
						 FROM	INFORMATION_SCHEMA.COLUMNS
						 WHERE	Table_Name = @Table 
							AND Ordinal_Position = @CurrOrd		)

		-- will be used to pull the column and also as it's title
		-- so it will be unique 
		SET @ColumnName = @Alias + '.' + @CurrCol

		-- append to @SQL the alias.columnName followed by a comma
		SET @SQL = @SQL + @ColumnName + ' ' + @SQ + @ColumnName + @SQ + ', '

		-- get the next column ordinal
		SET @CurrOrd = ( SELECT	MIN(Ordinal_Position)
						 FROM	INFORMATION_SCHEMA.COLUMNS
						 WHERE	Table_Name = @Table 
							AND Ordinal_Position > @CurrOrd		)


	END

	SET @SQL = LEFT(@SQL, LEN(@SQL) - 1 )			-- strip off the last comma

	-- create the SQL for the table
	SET @SQL = 'SELECT ' + @TAB + @SQL + @LF		 
	SET @SQL = @SQL + 'FROM ' + @TAB + 'dbo.' + @Table + ' ' + @Alias

	-- display it to the screen
	PRINT @SQL