SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_generate_audit_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_generate_audit_table]
GO

CREATE procedure dbo.p_generate_audit_table
	@SOURCE_TBLNM VARCHAR(128),
	@DEST_TBLNM_PREFIX VARCHAR(10) = 'a_'

AS

BEGIN 


    SET NOCOUNT ON

--DECLARE VARIABLES
    declare @cmd varchar(4000),
	    @minid int,
	    @maxid int,
	    @DEST_TBLNM varchar(128),
	    @final_cmd varchar(8000),
	    @sql_delimiter varchar(25)

create table #tmp_tblnm ( tblnm varchar(100) )
	   
-- TABLE TO HOLD SOURCE COLUMN 
    create table #T_columns  (
	TABLE_QUALIFIER nvarchar(128),
	TABLE_OWNER nvarchar(128),
	TABLE_NAME nvarchar(128),
	COLUMN_NAME nvarchar(128),
	DATA_TYPE smallint,
	CTYPE_NAME varchar(25) ,
	CPRECISION int ,
	LENGTH int ,
	SCALE smallint ,
	RADIX smallint ,
	NULLABLE smallint ,
	REMARKS varchar(254) ,
	COLUMN_DEF nvarchar(3000) ,
	SQL_DATA_TYPE smallint ,
	SQL_DATETIME_SUB smallint ,
	CHAR_OCTET_LENGTH int ,
	ORDINAL_POSITION int ,
	IS_NULLABLE varchar(254) ,
	SS_DATA_TYPE tinyint )

--SET VARIABLES
    select @DEST_TBLNM = @DEST_TBLNM_PREFIX + substring(@SOURCE_TBLNM, 1,128) ,@sql_delimiter = '
GO' + '

',
@final_cmd = ''

    
--  LOAD TEMP TABLE WITH SOURCE TABLE

set @cmd = 'exec dbo.sp_columns @table_name = ''' + @SOURCE_TBLNM + ''''

    insert into #T_columns( 
	TABLE_QUALIFIER , 
	TABLE_OWNER , 
	TABLE_NAME ,
	COLUMN_NAME , 
	DATA_TYPE , 
	CTYPE_NAME  ,
	CPRECISION  , 
	LENGTH  , 
	SCALE  , 
	RADIX  ,
	NULLABLE  , 
	REMARKS  , 
	COLUMN_DEF  ,
	SQL_DATA_TYPE  , 
	SQL_DATETIME_SUB  , 
	CHAR_OCTET_LENGTH  ,
	ORDINAL_POSITION  , 
	IS_NULLABLE  , 
	SS_DATA_TYPE)
    exec( @cmd)


--  BUILD AND EXECUTE THE STATEMENT THAT WILL COPY THE SOURCE TABLE 
--get the min/max ordinal #'s (used to loop thru all columns)
    SELECT @minid = min(ORDINAL_POSITION), 
	   @maxid = max(ORDINAL_POSITION) 
    from #T_columns

--BUILD THE AUDIT TABLE STATEMENT


    select @cmd = 'CREATE TABLE dbo.'+ @DEST_TBLNM + ' ( 
	audit_ID  int identity(1,1)  not null PRIMARY KEY CLUSTERED, 
	modid int not null ,  '

    while (@minid <= @maxid) 
	begin

	--add current column to the sql statement, perform some validations
	select @cmd = @cmd + ' 
	' + column_name + 
		    CASE 
		        WHEN left(CTYPE_NAME,12) = 'int identity' THEN 
			    ' int '
		        WHEN left(CTYPE_NAME,15) = 'bigint identity'  THEN
			    ' bigint '
		    	ELSE 
			    ' ' + Ctype_name
		    	END +
		    CASE 
	        	WHEN Ctype_name = 'varchar' or Ctype_name = 'char' 
			OR Ctype_name = 'Nvarchar' or Ctype_name = 'Nchar' THEN 
		    	    '(' + cast(CPRECISION as varchar(4)) + ') '
			WHEN CTYPE_NAME = 'numeric' OR CTYPE_NAME = 'DECIMAL' THEN 
		    	    ' ( ' + CAST(CPRECISION AS VARCHAR) + ',' + CAST(SCALE AS VARCHAR) + ') '
			ELSE 
			    ' '
		    	END +
 		    CASE 
			WHEN NULLABLE = 1 THEN 
			    ' NULL '
			ELSE 
			    ' NOT NULL '
			END +   ','
        from #T_columns 
        where ORDINAL_POSITION = @minid

	--move to the next column
	set @minid = @minid + 1
    end

    --add close paren
    set @cmd = left(@cmd, len(@cmd) - 1) + ' ) '


--drop the source table if it exists
			set @final_cmd = @final_cmd + ' 
if exists (select * from dbo.sysobjects where name = ''' + @DEST_TBLNM + ''' and type = ''U'') 
	drop table dbo.' + @DEST_TBLNM + @sql_delimiter

--ADD BUILD TABLE STATEMENT 
	set @final_cmd = @final_cmd + @cmd + @sql_delimiter
  
set @final_cmd = '--**************************************************
-- Table ' + @DEST_TBLNM  + ' 
--**************************************************
' + @final_cmd + '--**************************************************'  + ' 
'

print @final_cmd


end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO