if exists (select * from sysobjects where id = object_id('dbo.sp_Select') and sysstat & 0xf = 4)
	drop procedure dbo.sp_Select
GO

Create Procedure sp_Select
@tablename varchar(30)
as
begin

/********************************************************************************************************************
**Author:	Ron West 
**		Omzig Productions, Inc 
**		1/12/1997
** Purpose:
**	This procedure will build a Select statement for you with all of the feilds that are involved in the table
**
**	Special Note:
**		The results will appear differently when the Sum(datalength) of the column names > 255 chars
********************************************************************************************************************/
Set nocount on
DECLARE @TableNameHeader  varchar(75)
Declare @ColName varchar(255)
Declare @ColList varchar(255)
Declare @SumVal int
Declare @Incr int
Declare @NumCols int

Select @TableNameHeader ='FROM ' + @Tablename

Select @SumVal=Sum(Datalength(name)),@NumCols=Count(*) FROM syscolumns WHERE id=(Select id from sysobjects where type="u" and  name=@TableName)

if @SumVal=0 or @SumVal=null
Begin
	Print 'Specified Table Not Found'
	Return	/* Bail out here */
End

If @SumVal < 255
Begin
	/* returns the select as all one line when the statement will fit in one 255 character string */
	DECLARE small_cursor CURSOR FOR SELECT name FROM syscolumns WHERE id=(Select id from sysobjects where type="u" and  name=@TableName) FOR READ ONLY
	OPEN small_cursor 				/*open the cursor*/
	FETCH NEXT FROM small_cursor INTO @colname	/*Get the 1st row*/
	WHILE (@@fetch_status <> -1)			/*set into loop until no more data can be found*/
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			SELECT @ColName = RTRIM(@colname) + ","
			Select @ColList=@ColList + @ColName
		END
		FETCH NEXT FROM small_cursor INTO @Colname /* get the next row*/
	END
	Close small_cursor
	DEALLOCATE small_cursor
	If datalength(@ColList) > 0 
	Begin
		Select @ColList=substring(@ColList,1,Datalength(@ColList)-1)

		Select @ColList='SELECT ' + @ColList  + ' ' + @TableNameHeader
		Print @ColList
		Return  				/* Bail out here */
	End
end

Else 
Begin	/* now for the ugly part, since we can't return the whole thing at once, each column goes it's own line */
	Select @Incr=0

	DECLARE BIG_cursor CURSOR FOR SELECT name FROM syscolumns WHERE id=(Select id from sysobjects where type="u" and  name=@TableName) FOR READ ONLY
	OPEN BIG_cursor 				/*open the cursor*/
	FETCH NEXT FROM BIG_cursor INTO @colname	/*Get the 1st row*/
	WHILE (@@fetch_status <> -1)			/*set into loop until no more data can be found*/
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			/* what have to here is figure out if were are on the last item, if so don't put a comma after it */
			Select @Incr=@Incr +1
			If @Incr=@NumCols
				SELECT @ColName = Char(9) + RTRIM(@colname)
			Else
			Begin	
				If @Incr=1
					Select @ColName = 'SELECT ' + RTRIM(@colname) + ','
				Else
					Select @ColName = Char(9) + RTRIM(@colname) + ','
			End		
			
			Print @ColName
		END
		FETCH NEXT FROM BIG_cursor INTO @Colname /* get the next row*/
	END
	Close BIG_cursor
	DEALLOCATE BIG_cursor



	Print @TableNameHeader /* Print the FROM Statement */

End

end
GO

GRANT  EXECUTE  ON dbo.sp_Select  TO public
GO