Sql 2008 stored procedure for creating history tables out of passed existing table

USE [MyDb]

GO

/****** Object:  StoredProcedure [dbo].[procUtils_GenerateHistoryTable]    Script Date: 10/21/2009 09:56:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter PROCEDURE [dbo].[procUtils_GenerateHistoryTable]

@TableName [varchar](50)

WITH EXECUTE AS CALLER

AS

BEGIN -- proc start                                                

SET NOCOUNT ON;                                                

BEGIN TRY        --begin try                                      

--CODE SNIPPET TO LIST TABLE COLUMNS                     

-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE                    

--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name                     

--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'                    

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'                        

--Declare the Table variable                         

DECLARE @ColNames TABLE                        

(                        

Number INT IDENTITY(1,1), --Auto incrementing Identity column                        

ColName VARCHAR(300) , --The string value                        

DataType varchar(50) , --the datatype               

IsNullable varchar(3) -- whether or not this table col is nullable

)                        

--Decalre a variable to remember the position of the current delimiter                        

DECLARE @CurrentDelimiterPositionVar INT                         

--Decalre a variable to remember the number of rows in the table                        

DECLARE @Count INT   

DECLARE @sqlTxtToPrint varchar(max)

SET @sqlTxtToPrint = ' USE MyDb 

GO'

--Populate the TABLE variable using some logic                        

-- SELECT *  from INFORMATION_SCHEMA.COLUMNS         

INSERT INTO @ColNames SELECT column_name , Data_type , IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS                     

where TABLE_NAME=@TableName                        

--Initialize the looper variable                        

SET @CurrentDelimiterPositionVar = 1                        

--Determine the number of rows in the Table                        

SELECT @Count=max(Number) from @ColNames                        

--A variable to hold the currently selected value from the table                        

DECLARE @ColumnName varchar(300);                        

DECLARE @DataType varchar(50)              

DECLARE @PkColName varchar(200)

DECLARE @IsNullable varchar(3)

set @sqlTxtToPrint = '              

SET NOCOUNT ON;              

SET XACT_ABORT ON;              

GO 

DROP TABLE [dbo].[' + @TableName + 'History]

GO

CREATE TABLE [dbo].[' + @TableName + 'History](' + CHAR(13)              

--Loop through until all row processing is done                        

WHILE @CurrentDelimiterPositionVar <= @Count                        

BEGIN                        

--Load current value from the Table                        

SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar  

SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar    

SELECT @IsNullable = IsNullable FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar    

--PRINT EACH COLUMN WITH , AT THE END 

if @CurrentDelimiterPositionVar = 1 

BEGIN --IF THIS IS THE FIRST COL = PK

set @PkColName = @TableName + 'HistoryId' 

set @sqlTxtToPrint = @sqlTxtToPrint +  

'[' + @PkColName + '][int] IDENTITY(1,1) NOT NULL,' + CHAR(13)

END --IF THIS IS THE FIRST COL = PK

IF @IsNullable = 'YES'

BEGIN --IF @IsNullable = 'YES'

set @sqlTxtToPrint = @sqlTxtToPrint +  

'[' + @ColumnName + ']['  + @DataType  +     

'] NULL ,'+ CHAR(13)

end --IF @IsNullable = 'YES'

else 

begin --IF @IsNullable = 'NO'

set @sqlTxtToPrint = @sqlTxtToPrint +  

'[' + @ColumnName + ']['  + @DataType  +     

'] NOT NULL,'+ CHAR(13)

end --IF @IsNullable = 'NO'

-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName                    

-- print @ColumnName    --SIMPLE PRINT                    

--Increment loop counter                        

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1

END                        

set @sqlTxtToPrint = @sqlTxtToPrint +  '[SqlLogin] [varchar](50) NOT NULL,

[Action] [varchar](50) NOT NULL,

[UpdatedWhen] [datetime] NOT NULL 

CONSTRAINT [PK_' + @TableName + 'History] PRIMARY KEY CLUSTERED 

(

[' + @PkColName + '] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

'

--NOW ADD SOME USEFULL STATEMENTS 

set @sqlTxtToPrint = @sqlTxtToPrint +  

+ 'SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%''' + CHAR(13)       

+ 'SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%'''    + CHAR(13)    

+ 'exec sp_HelpText procUtils_GenerateMetaInsert'        + CHAR(13)

+ 'exec procUtils_GenerateMetaInsert ''TABLE'''        + CHAR(13)

--AT THE END PRINT THE RESULT 

PRINT @sqlTxtToPrint

END TRY        --end try                                      

BEGIN CATCH                                            

print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +                                   

'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +                     

CAST(ERROR_SEVERITY() AS varchar(10)) +                                   

'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +                     

'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                                            

END CATCH                                            

END --procedure end                                                 

/*                     

USE [MyDb]                    

GO                    

SELECT NAME FROM SYS.tables where name like '%Msg%'                    

EXEC [dbo].[procUtils_GenerateHistoryTable] @TableName = N'Feat'              

*/ 

GO