_Select Update

/********************************************************************************************************
** Original Name: _Select
** Name: ps_GetColumnNames
** 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: Database name, Table name, an alias and a column alias bit
** Retr: select with all the columns from tablename, followed by alias (optional)
** Original Auth: D Simmons 
** Updated By: J Rice
**
** Mod Date:
** 05.15.07 - dts Original version
** 05.21.07 - 1. Added dynamic feature to query tables in other databases (not linked servers) from one sp.
**            2. Enabled table name to be sent with owner qualification.
**    3. Added column alias bit parameter to enable/disable column aliases.
*********************************************************************************************************/
alter PROCEDURE [dbo].[_Select] (
@DBVARCHAR(100),
@TableVARCHAR(100),
@AliasVARCHAR(20),
@ColumnAlias bit = 0
)

AS

 SET NOCOUNT ON

-- ------------------------------------------------------------------------
-- DECLARATION and TABLE CREATION
-- ------------------------------------------------------------------------

DECLARE@CurrOrdINT,
@SQLVARCHAR(4000),
@CurrColVARCHAR(200),
@LFCHAR(1),
@TABCHAR(1),
@SQCHAR(1),
@ColumnName     VARCHAR(200),
@CurrOrd1_Sql   NVARCHAR(4000),
@CurrOrd2_Sql   NVARCHAR(4000),
@CurrCol_Sql    NVARCHAR(4000),
@Param          NVARCHAR(100)

-- ------------------------------------------------------------------------
-- INITIALIZE
-- ------------------------------------------------------------------------

SET @SQL = ''
SET @LF = CHAR(10)
SET @TAB = CHAR(9)
SET @SQ = CHAR(39)
--remove table owner if included in parameter
SET @Table = right(@Table, len(@Table) - charindex('.', @Table))
--dynamic sql to allow use in any db on server from one sp
SET @CurrOrd1_Sql = N' select @CO = ( SELECT MIN(Ordinal_Position) ' +
N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
    N' WHERE Table_Name = ''' + @Table + ''' )'
SET @CurrOrd2_Sql = N' select @CO = ( SELECT MIN(Ordinal_Position) ' +
N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
    N' WHERE Ordinal_Position > @CO ' +
     N' AND Table_Name = ''' + @Table + ''' )'
SET @CurrCol_Sql =  N' SELECT @CC = ( SELECT Column_Name ' +
     N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
     N' WHERE Ordinal_Position = @CO ' +
     N' AND Table_Name = ''' + @Table + ''' )'

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

-- get the first column for this table
select @Param = N'@CO int output'
exec sp_executesql @CurrOrd1_Sql, @Param, @CO = @CurrOrd output

-- 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
select @Param = N'@CO int, ' +
 N'@CC nvarchar(200) output'
exec sp_executesql @CurrCol_Sql, @Param, @CurrOrd, @CC = @CurrCol output

-- 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 if bit is set
SET @SQL = @SQL + case when @ColumnAlias = 0 
then @ColumnName + ' ' + @SQ + @ColumnName + @SQ + ', ' 
else @ColumnName + ', ' end

-- get the next column ordinal
select @Param = N'@CO int output'
exec sp_executesql @CurrOrd2_Sql, @Param, @CO = @CurrOrd output

END

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

-- 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