Web Listing 1: Stored Procedure 

CREATE procedure dbo.sp_CBI_BuildTable
	@DbName varchar(30),			-- Database to build Table
	@SrcName nvarchar(128),			-- Source Name (table or view)
	@TrgName nvarchar(128),			-- Target Table Name
	@OptionsFlag smallint = null,		-- Options Bit Flag
	@PostExecuteSP nvarchar(128) = null,	-- Stored Procedure executes at end before activating table
	@ExcludeIndexName nvarchar(128) = null,	-- Pattern String for indexes to exclude during build
	@SrcLocation varchar(128) = null,		-- Source system or Db
	@IndexTrgName nvarchar(128) = null,	-- Table to use for list of Indexes to build on target table
	@MinRowWarning int = 0			-- Minimum rows target table must contain unless failure
AS

Declare
	@SqlString varchar(8000),
	@TmpTrgName nvarchar(128),
	@RetStatus int,
	@Status int

-- OptionsFlag Bit Values
-- 1 (1) - Ansi Warnings on
-- 2 (2) - Create PK Index Only
-- 4 (3) - Create PK Index Only before PostExecuteSP then rest of indexes
-- 8 (4) - No Temporary table first
-- 16 (5) - Don't replace final table but keep built temp table (for testing) 


-- Set Ansi Warning if Option Set
IF @OptionsFlag & 1 = 1
BEGIN
	SET ANSI_NULLS ON
	SET ANSI_WARNINGS ON
END

-- Set SrcLocation String
IF @SrcLocation is null
	SET @SrcLocation = ''
ELSE
	SET @SrcLocation = @SrcLocation + '.'

-- Set Temp Table Name unless OptionsFlag indicates not
IF @OptionsFlag & 8 = 8
	SELECT @TmpTrgName = @TrgName
ELSE
	SELECT @TmpTrgName = 'T' + @TrgName

-- Set IndexTrgName
IF @IndexTrgName is null
	SET @IndexTrgName = @TrgName
	
-- Build Sql String
SELECT @SqlString = '
USE ' + @DbName + '

DECLARE @Status int
DECLARE @Start datetime,
	@Stop datetime,
	@TotalRows int,
	@Error int

SELECT @Start = getdate()
' +

-- Check For Source Object unless a SrcLocation was specified
CASE WHEN @SrcLocation = '' THEN
'
IF NOT EXISTS (Select name from sysobjects where name = '''+ @SrcName + ''')
BEGIN
	RAISERROR(''Source Table Not Found: ' + @SrcName + ''', 16, 1)
	GOTO ERRORHERE
END
' ELSE '' END +

'
IF EXISTS (Select name from sysobjects where name = ''' + @TmpTrgName + ''' and type = ''U'')
BEGIN
	EXEC @Status = CBI_NOVA..sp_CBI_KillObjectLocks ''' + @DbName + ''', ''' + @TmpTrgName + '''
	DROP TABLE dbo.' + @TmpTrgName + '

	IF @@error <> 0
	BEGIN
		RAISERROR(''Problem Dropping Temp Target Table.'', 16, 1)
		GOTO ERRORHERE
	END
END

SELECT *
INTO dbo.' + @TmpTrgName + '
FROM ' + @SrcLocation + @SrcName + '

SELECT @Error = @@error, @TotalRows = @@rowcount

IF @Error <> 0 or @TotalRows < ' + convert(varchar, @MinRowWarning) + '
BEGIN
	RAISERROR(''Problem Creating Target Table.'', 16, 1)
	GOTO ERRORHERE
END

EXEC @Status = CBI_NOVA.dbo.sp_CBI_IndexMaintenance ' + @IndexTrgName + ', ''Create'', 100, ' + 
@DbName + ', dbo, ' + @TmpTrgName + 
-- Build On PK Indexes if Options indicate
CASE WHEN @OptionsFlag & 2 = 2 OR (@OptionsFlag & 4 = 4 AND  @PostExecuteSP is not null) THEN ', 
''%PK%'', ' + @ExcludeIndexName ELSE '' END + 

'
IF @@error <> 0 or @Status <> 0
BEGIN
	RAISERROR(''Index Creation Failed.'', 16, 1)
	GOTO ERRORHERE
END
' +

-- Execute PostExecuteSP if provided
CASE WHEN @PostExecuteSP is not null THEN 
'
EXEC @Status = ' + @PostExecuteSP + '

IF @@error <> 0 OR @Status <> 0
BEGIN
	RAISERROR(''Unable To Run Post SP'', 16, 1)
	GOTO ERRORHERE
END
' ELSE '' END + 

-- Build Rest of indexes if option indicated to build only PK first
CASE WHEN @OptionsFlag & 4 = 4 AND @PostExecuteSP is not null
THEN 'EXEC @Status = CBI_NOVA.dbo.sp_CBI_IndexMaintenance ' + @IndexTrgName + ', ''Create'', 100, ' 
+ @DbName + ', dbo, ' + @TmpTrgName +
'
IF @@error <> 0 OR @Status <> 0
BEGIN
	RAISERROR(''Unable To Build Rest of Indexes.'', 16, 1)
	GOTO ERRORHERE
END
'
ELSE '' END +


-- Drop Original table and rename temp unless option was to drop original at beginning or not to replace 
original
CASE WHEN @OptionsFlag & 8 = 8 OR @OptionsFlag & 16 = 16

THEN '' 
ELSE 
'

IF EXISTS (Select name from sysobjects where name = ''' + @TrgName + ''' and type = ''U'')
BEGIN
	EXEC @Status = CBI_NOVA..sp_CBI_KillObjectLocks ''' + @DbName + ''', ''' + @TrgName + '''
	DROP TABLE dbo.' + @TrgName + '

	IF @@error <> 0
	BEGIN
		RAISERROR(''Problem Dropping Original Target Table.'', 16, 1)
		GOTO ERRORHERE
	END
END

EXEC @Status = sp_rename ' + @TmpTrgName + ', ' + @TrgName + '

IF @@error <> 0 OR @Status <> 0
BEGIN
	RAISERROR(''Unable To Rename Tables'', 16, 1)
	GOTO ERRORHERE
END
' 
END
+

-- Post Time and RowCount of build
'
SELECT @Stop = getdate()

INSERT INTO CBI_NOVA.dbo.NOVA_BUILDTABLE_LOG (DbName, TableName, BuildStart, BuildStop, 
TotalRows)
VALUES (''' + @DbName + ''', ''' + @TrgName + ''', @Start, @Stop, @TotalRows )

GOTO EXITHERE

ERRORHERE:
print ''Error''
GOTO DONE

EXITHERE:
print ''Done''

DONE:
'

print @SqlString

EXEC(@SqlString)

IF @@error <> 0
BEGIN
	print 'Error'
	RETURN 1
END
GO