AUDIT WITH A TWIST

/*READ ME
STEPS FOR AUDIT:

RUN THE AUDIT_DETAIL_MASTER.sql SCRIPT TO CREATE THE PROCEDURES

1. RUN SP_CREATEAUDITTABLES - CREATES AUDIT TABLES
2. RUN SP_ALTAUDTABLES - ALTERS THE AUDIT TABLES
3. RUN SP_AUDTRIG - CREATES THE I,U, D TRIGGERS

TO CLEAN

1. RUN SP_CLEANAUDTABLES - DROPS THE TABLES
2. RUN SP_CLEANAUDTR - DROPS THE TRIGGERS

exec SP_CREATEAUDITTABLES
exec SP_ALTAUDTABLES
exec SP_AUDTRIG

exec SP_CLEANAUDTR
exec SP_CLEANAUDTABLES

*/

/****** Object:  Stored Procedure dbo.SP_ALTAUDTABLES    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_ALTAUDTABLES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_ALTAUDTABLES]
GO

/****** Object:  Stored Procedure dbo.SP_AUDTRIG    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_AUDTRIG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_AUDTRIG]
GO

/****** Object:  Stored Procedure dbo.SP_CLEANAUDTABLES    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CLEANAUDTABLES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CLEANAUDTABLES]
GO

/****** Object:  Stored Procedure dbo.SP_CLEANAUDTR    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CLEANAUDTR]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CLEANAUDTR]
GO

/****** Object:  Stored Procedure dbo.SP_CREATEAUDITTABLES    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CREATEAUDITTABLES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CREATEAUDITTABLES]
GO

/****** Object:  Stored Procedure dbo.SP_TABLECOMPARE    Script Date: 2/13/2007 4:39:11 PM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_TABLECOMPARE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_TABLECOMPARE]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_ALTAUDTABLES    Script Date: 2/13/2007 4:39:11 PM ******/
/****** Object:  Stored Procedure dbo.SP_ALTAUDTABLES    Script Date: 2/13/2007 4:36:35 PM ******/CREATE PROCEDURE SP_ALTAUDTABLES
/*ALTERS THE AUDIT TABLES TO ADD THE USERID AND CHANGEDON FIELDS IF THEY DO NOT EXIST ON THE SOURCE TABLE AND 
ADD THE ACTIONX FIELD TO THE AUDIT TABLE IF IT DOES NOT EXIST
AUTHOR : CLIFFORD SAMUELSON
DATE: 13/07/2007
*/AS
DECLARE @sourceid AS INT
DECLARE @SOURCEID_AUD AS INT
DECLARE @USERID AS INT
DECLARE @A AS INT
DECLARE @B AS INT
DECLARE @C AS INT
DECLARE @TNAME AS VARCHAR (500)
DECLARE @STR1 AS VARCHAR (8000)
DECLARE @STR2 AS VARCHAR (8000)
DECLARE @STR3 AS VARCHAR (8000)
DECLARE CT_AT CURSOR FOR
select [name] from sysobjects where xtype='U' and [name] <> 'dtproperties' AND NAME NOT LIKE 'AUD_%' 
OPEN CT_AT
FETCH NEXT FROM CT_AT INTO @TNAME
WHILE (@@FETCH_STATUS = 0 )
BEGIN

create table #v1
(
a varchar (20),
b varchar (20),
C VARCHAR (20))

insert into #v1 (a,b,C) values ('USERID','CHANGEDON','ACTIONX') 

/*
GET THE USER ID
*/SELECT @userid = uid
FROM sysusers 
WHERE name = 'DBO'

/*
GET THE SOURCE ID
*/SET @sourceid = (SELECT so.id FROM sysobjects so, sysusers su
WHERE su.uid = @userid
AND su.uid = so.uid
AND so.name = @TNAME)

/*
GET THE COLUMNS OF THE TABLE AND CHECK TO SEE IF THE "USERID" AND "CHANGEDON" FIELDS HAVE BEEN ADDED TO THE TABLE
IF NOT ADDED THEN WE WILL ADD IT INTO THE AUDIT TABLE
*/Print 'TABLE NAME:'+@tname
print +@sourceid
  set @A = (select (isnull((SELECT 1 FROM #V1 WHERE A IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype)), -1)) from #v1)

set @B = (select (isnull((SELECT 1 FROM #V1 WHERE B IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype)), -1)) from #v1)

IF @A = -1 
SELECT @STR1 = 'ALTER TABLE '+'['+'AUD_' + @TNAME + ']'+' ADD USERID INT'
print @str1
EXEC (@STR1)

IF @B = -1 
SELECT @STR2 = 'ALTER TABLE '+'['+'AUD_' + @TNAME +']'+ ' ADD CHANGEDON AS (GETDATE())'
print @str2
EXEC (@STR2)

/*
HERE WE ADD THE ACTIONFIELD TO THE AUDIT TABLE
*/
/*
GET THE SOURCE ID OF THE AUDIT TABLE
*/SET @sourceid_AUD = (SELECT so.id FROM sysobjects so, sysusers su
WHERE su.uid = @userid
AND su.uid = so.uid
AND so.name = 'AUN_'+@TNAME)


SET @C = (select (isnull((SELECT 1 FROM #V1 WHERE C IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid_AUD
AND sc.xtype = st.xtype)), -1)) from #v1)

IF @C = -1 
SELECT @STR3 = 'ALTER TABLE '+'['+'AUD_'+ @TNAME + ']'+' ADD ACTIONX VARCHAR(1)' 
print @str3
EXEC (@STR3)

drop table #v1

FETCH NEXT FROM CT_AT INTO @TNAME
END
CLOSE CT_AT
DEALLOCATE CT_AT




GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_AUDTRIG    Script Date: 2/13/2007 4:39:11 PM ******/CREATE PROCEDURE SP_AUDTRIG
/*THIS PROCEDURE CREATES THE AUDIT TRIGGERS FOR ALL THE TABLES THAT GETS AUDIT.
AUTHOR : CLIFFORD SAMUELSON
DATE: 13/07/2007
*/AS
DECLARE @sourceid AS INT
DECLARE @SOURCEID_AUD AS INT
DECLARE @USERID AS INT
DECLARE @A AS INT
DECLARE @B AS INT
DECLARE @C AS INT
DECLARE @TNAME AS VARCHAR (500)
DECLARE @STR1 AS VARCHAR (8000)
DECLARE @STR2 AS VARCHAR (8000)
DECLARE @STR3 AS VARCHAR (8000)
DECLARE @STR4 AS VARCHAR (8000)
DECLARE @STR5 AS VARCHAR (8000)
DECLARE @STR6 AS VARCHAR (8000)
DECLARE @STR7 AS VARCHAR (8000)
DECLARE @STR8 AS VARCHAR (8000)
DECLARE @STR9 AS VARCHAR (8000)
DECLARE @STR10 AS VARCHAR (8000)
DECLARE @STR11 AS VARCHAR (8000)
DECLARE @STR12 AS VARCHAR (8000)
DECLARE @STR13 AS VARCHAR (8000)
DECLARE @STR14 AS VARCHAR (8000)
DECLARE @session_usr char(30)
DECLARE CT_TR CURSOR FOR
select [name] from sysobjects where xtype='U' and [name] <> 'dtproperties' AND NAME NOT LIKE 'AUD_%' 
OPEN CT_TR
FETCH NEXT FROM CT_TR INTO @TNAME
WHILE (@@FETCH_STATUS = 0 )
BEGIN

create table #v1
(
a varchar (20),
b varchar (20),
C VARCHAR (20))

insert into #v1 (a,b,C) values ('USERID','CHANGEDON','ACTIONX') 

/*
GET THE USER ID
*/SELECT @userid = uid
FROM sysusers 
WHERE name = 'DBO'

/*
GET THE SOURCE ID
*/SET @sourceid = (SELECT so.id FROM sysobjects so, sysusers su
WHERE su.uid = @userid
AND su.uid = so.uid
AND so.name = @TNAME)

/*
GET THE COLUMNS OF THE TABLE AND CHECK TO SEE IF THE "USERID" AND "CHANGEDON" FIELDS HAVE BEEN ADDED TO THE TABLE
IF NOT ADDED THEN WE WILL ADD IT INTO THE AUDIT TABLE
*/Print 'TABLE NAME:'+@tname
print +@sourceid
  set @A = (select (isnull((SELECT 1 FROM #V1 WHERE A IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype)), -1)) from #v1)

set @B = (select (isnull((SELECT 1 FROM #V1 WHERE B IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype)), -1)) from #v1)



/*TRIGGER CREATION*/PRINT @A
PRINT @B
IF @A = -1 AND @B = -1 

--UPDATE
SELECT @STR1 = 'CREATE TRIGGER  '+'['+'TR_AUD_U' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR UPDATE AS '
SELECT @STR1 = @STR1 + ' BEGIN '
SELECT @STR1 = @STR1 + ' DECLARE @session_usr char(30)'
SELECT @STR1 = @STR1 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR1 = @STR1 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR1 = @STR1 + ' SELECT *, @session_usr, ''U''   FROM DELETED '
SELECT @STR1 = @STR1 + ' END'
print @str1
EXEC (@STR1)
--DELETE
SELECT @STR7 = 'CREATE TRIGGER  '+'['+'TR_AUD_D' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR DELETE AS '
SELECT @STR7 = @STR7 + ' BEGIN '
SELECT @STR7 = @STR7 + ' DECLARE @session_usr char(30)'
SELECT @STR7 = @STR7 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR7 = @STR7 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR7 = @STR7 + ' SELECT *, @session_usr, ''D''   FROM DELETED '
SELECT @STR7 = @STR7 + ' END'
print @str7
EXEC (@STR7)
--INSERT
SELECT @STR11 = 'CREATE TRIGGER  '+'['+'TR_AUD_I' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR INSERT AS '
SELECT @STR11 = @STR11 + ' BEGIN '
SELECT @STR11 = @STR11 + ' DECLARE @session_usr char(30)'
SELECT @STR11 = @STR11 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR11 = @STR11 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR11 = @STR11 + ' SELECT *, @session_usr, ''I''   FROM INSERTED '
SELECT @STR11 = @STR11 + ' END'
print @str11
EXEC (@STR11)

IF @A = 1 AND @B = -1

--UPDATE
SELECT @STR4 = 'CREATE TRIGGER  '+'['+'TR_AUD_U' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR UPDATE AS '
SELECT @STR4 = @STR4 + ' BEGIN'
SELECT @STR4 = @STR4 + ' DECLARE @session_usr char(30)'
SELECT @STR4 = @STR4 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR4 = @STR4 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR4 = @STR4 + ' SELECT *, ''U''  FROM DELETED '
SELECT @STR4 = @STR4 + ' END'
print @str4
EXEC (@STR4)
--DELETE
SELECT @STR8 = 'CREATE TRIGGER  '+'['+'TR_AUD_D' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR DELETE AS '
SELECT @STR8 = @STR8 + ' BEGIN'
SELECT @STR8 = @STR8 + ' DECLARE @session_usr char(30)'
SELECT @STR8 = @STR8 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR8 = @STR8 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR8 = @STR8 + ' SELECT *, ''D''  FROM DELETED '
SELECT @STR8 = @STR8 + ' END'
print @str8
EXEC (@STR8)
--INSERT
SELECT @STR12 = 'CREATE TRIGGER  '+'['+'TR_AUD_I' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR INSERT AS '
SELECT @STR12 = @STR12 + ' BEGIN'
SELECT @STR12 = @STR12 + ' DECLARE @session_usr char(30)'
SELECT @STR12 = @STR12 + ' SET @session_usr = (select user_id (SESSION_USER)) '
SELECT @STR12 = @STR12 + ' INSERT '+'['+'AUD_'+@TNAME +']'
SELECT @STR12 = @STR12 + ' SELECT *, ''I''  FROM INSERTED '
SELECT @STR12 = @STR12 + ' END'
print @str12
EXEC (@STR12)

IF @A = 1 AND @B = 1

--UPDATE
SELECT @STR5 = 'CREATE TRIGGER  '+'['+'TR_AUD_U' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR UPDATE AS '
SELECT @STR5 = @STR5 + ' BEGIN'
SELECT @STR5 = @STR5 + ' DECLARE @session_usr char(30) '
SELECT @STR5 = @STR5 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR5 = @STR5 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR5 = @STR5 + ' SELECT *, ''U'' FROM DELETED  '
SELECT @STR5 = @STR5 + ' END '
print @str5
EXEC (@str5)
--DELETE
SELECT @STR9 = 'CREATE TRIGGER  '+'['+'TR_AUD_D' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR DELETE AS '
SELECT @STR9 = @STR9 + ' BEGIN'
SELECT @STR9 = @STR9 + ' DECLARE @session_usr char(30) '
SELECT @STR9 = @STR9 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR9 = @STR9 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR9 = @STR9 + ' SELECT *, ''D'' FROM DELETED  '
SELECT @STR9 = @STR9 + ' END '
print @str9
EXEC (@str9)
--INSERT
SELECT @STR13 = 'CREATE TRIGGER  '+'['+'TR_AUD_I' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR INSERT AS '
SELECT @STR13 = @STR13 + ' BEGIN'
SELECT @STR13 = @STR13 + ' DECLARE @session_usr char(30) '
SELECT @STR13 = @STR13 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR13 = @STR13 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR13 = @STR13 + ' SELECT *, ''I'' FROM INSERTED  '
SELECT @STR13 = @STR13 + ' END '
print @str13
EXEC (@str13)

IF @A = -1 AND @B = 1

--UPDATE
SELECT @STR6 = 'CREATE TRIGGER  '+'['+'TR_AUD_U' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR UPDATE AS '
SELECT @STR6 = @STR6 + ' BEGIN '
SELECT @STR6 = @STR6 + ' DECLARE @session_usr char(30) '
SELECT @STR6 = @STR6 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR6 = @STR6 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR6 = @STR6 + ' SELECT *,'+ @session_usr+ ', ''U''   FROM DELETED '
SELECT @STR6 = @STR6 + ' END '
PRINT @STR6
EXEC (@STR6)
--DELETE
SELECT @STR10 = 'CREATE TRIGGER  '+'['+'TR_AUD_D' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR DELETE AS '
SELECT @STR10 = @STR10 + ' BEGIN '
SELECT @STR10 = @STR10 + ' DECLARE @session_usr char(30) '
SELECT @STR10 = @STR10 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR10 = @STR10 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR10 = @STR10 + ' SELECT *,'+ @session_usr+ ', ''D''   FROM DELETED '
SELECT @STR10 = @STR10 + ' END '
PRINT @STR10
EXEC (@STR10)
--INSERT
SELECT @STR14 = 'CREATE TRIGGER  '+'['+'TR_AUD_I' + @TNAME + ']'+' ON '+ '['+ @TNAME + ']'+ ' FOR INSERT AS '
SELECT @STR14 = @STR14 + ' BEGIN '
SELECT @STR14 = @STR14 + ' DECLARE @session_usr char(30) '
SELECT @STR14 = @STR14 + ' SET @session_usr = (select user_id (SESSION_USER))  '
SELECT @STR14 = @STR14 + ' INSERT '+'['+'AUD_'+@TNAME +'] '
SELECT @STR14 = @STR14 + ' SELECT *,'+ @session_usr+ ', ''I''   FROM INSERTED '
SELECT @STR14 = @STR14 + ' END '
PRINT @STR14
EXEC (@STR14)

--IF @B = -1 
--SELECT @STR2 = 'ALTER TABLE '+'['+'AUD_' + @TNAME +']'+ ' ADD CHANGEDON DATETIME'
--print @str2
--EXEC (@STR2)

/*
HERE WE ADD THE ACTIONFIELD TO THE AUDIT TABLE
*/
/*
GET THE SOURCE ID OF THE AUDIT TABLE
*/--SET @sourceid_AUD = (SELECT so.id FROM sysobjects so, sysusers su
--WHERE su.uid = @userid
/*AND su.uid = so.uid
AND so.name = 'AUN_'+@TNAME)


SET @C = (select (isnull((SELECT 1 FROM #V1 WHERE C IN (SELECT sc.name
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid_AUD
AND sc.xtype = st.xtype)), -1)) from #v1)

IF @C = -1 
SET @STR3 = 'ALTER TABLE '+'['+'AUD_'+ @TNAME + ']'+' ADD ACTIONX VARCHAR(1)' 
SET @
print @str3
EXEC (@STR3)*/
drop table #v1

FETCH NEXT FROM CT_TR INTO @TNAME
END
CLOSE CT_TR
DEALLOCATE CT_TR








GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_CLEANAUDTABLES    Script Date: 2/13/2007 4:39:11 PM ******/
/****** Object:  Stored Procedure dbo.SP_CLEANAUDTABLES    Script Date: 2/13/2007 4:36:35 PM ******/CREATE PROCEDURE SP_CLEANAUDTABLES
/*DROPS ALL THE TABLES FOR THE AUDIT
AUTHOR : CLIFFORD SAMUELSON
DATE: 13/07/2007
*/AS 
DECLARE @TNAME AS VARCHAR (500)
DECLARE @STR AS NVARCHAr (1000)
DECLARE  CS_TC CURSOR FOR
select [name] from sysobjects where xtype='U' and [name] <> 'dtproperties' AND NAME LIKE 'AUD_%'
OPEN CS_TC
FETCH NEXT FROM CS_TC INTO @TNAME
WHILE (@@FETCH_STATUS =0 )
BEGIN
SELECT @STR = 'DROP TABLE ' + '['+@TNAME+']'
PRINT @STR
EXEC SP_EXECUTESQL @STR
FETCH NEXT FROM CS_TC INTO @TNAME
END
CLOSE CS_TC
DEALLOCATE CS_TC


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_CLEANAUDTR    Script Date: 2/13/2007 4:39:11 PM ******/
/****** Object:  Stored Procedure dbo.SP_CLEANAUDTR    Script Date: 2/13/2007 4:36:35 PM ******/CREATE PROCEDURE SP_CLEANAUDTR
/*THIS PROCEDURE DROPS THE AUDIT TRIGGERS FOR ALL THE TABLES THAT GETS AUDIT.
AUTHOR : CLIFFORD SAMUELSON
DATE: 13/07/2007
*/AS
DECLARE @TNAME AS VARCHAR (500)
DECLARE @STR15 AS VARCHAR (8000)
DECLARE @STR16 AS VARCHAR (8000)
DECLARE @STR17 AS VARCHAR (8000)
DECLARE CT_CLTR CURSOR FOR 
select [name] from sysobjects where xtype='U' and [name] <> 'dtproperties' AND NAME NOT LIKE 'AUD_%' 
OPEN CT_CLTR
FETCH NEXT FROM CT_CLTR INTO @TNAME
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @STR15 = 'DROP TRIGGER '+'['+'TR_AUD_U'+ @TNAME +']'
EXEC (@STR15)
PRINT @STR15

SET @STR16 = 'DROP TRIGGER '+'['+'TR_AUD_D'+ @TNAME +']'
EXEC (@STR16)
PRINT @STR16

SET @STR17 = 'DROP TRIGGER '+'['+'TR_AUD_I'+ @TNAME +']'
EXEC (@STR17)
PRINT @STR17
FETCH NEXT FROM CT_CLTR INTO @TNAME
END
CLOSE CT_CLTR
DEALLOCATE CT_CLTR


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_CREATEAUDITTABLES    Script Date: 2/13/2007 4:39:11 PM ******/
/****** Object:  Stored Procedure dbo.SP_CREATEAUDITTABLES    Script Date: 2/13/2007 4:36:35 PM ******/create procedure SP_CREATEAUDITTABLES 
/*CREATE THE TABLES FOR THE AUDIT
AUTHOR : CLIFFORD SAMUELSON
DATE: 13/07/2007
*/as 
declare @tname as varchar (500)
DECLARE @STR AS VARCHAR (500)
DECLARE @STR1 AS VARCHAR (500)
DECLARE @STR2 AS VARCHAR (500)
DECLARE @STR3 AS VARCHAR (10)
DECLARE @GO AS NVARCHAR (10)
DECLARE CS_T CURSOR FOR
select [name] from sysobjects where xtype='U' and [name] <> 'dtproperties' AND NAME NOT LIKE 'AUD_%' 
OPEN CS_T
FETCH NEXT FROM CS_T INTO @TNAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @STR1 = 'DBO'
select @STR2 = 'AUD_'+ @TNAME
SELECT @STR3 = '1'
SELECT @STR = 'EXEC SP_TABLECOMPARE ' + @STR1 +',' + '''' + @TNAME +''''+','+ ''''+'['+@STR2+']'+'''' +','+ @STR3
--PRINT @STR
EXEC (@STR)
FETCH NEXT FROM CS_T INTO @TNAME
END
CLOSE CS_T
DEALLOCATE CS_T


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SP_TABLECOMPARE    Script Date: 2/13/2007 4:39:11 PM ******/
/****** Object:  Stored Procedure dbo.SP_TABLECOMPARE    Script Date: 2/13/2007 4:36:35 PM ******/CREATE PROCEDURE SP_TABLECOMPARE @strUser_nm VARCHAR(10), @sourcename VARCHAR(40), 
@modifyname VARCHAR(40), @change TINYINT OUTPUT

AS

SET NOCOUNT ON
SET ARITHABORT ON 

--assume no changes at this point.  
SET @change = 0

DECLARE @sourceid INT, @modifyid INT, @userid INT, @field VARCHAR(42)
DECLARE @datatype VARCHAR(40), @length INT, @strsql VARCHAR(8000)
DECLARE @QtrTable VARCHAR(10), @index VARCHAR(8000)

--get the uid for the supplied study
SELECT @userid = uid
FROM sysusers 
WHERE name = @strUser_nm

--check to make sure the sourcename table exists
SET @sourceid = (SELECT so.id FROM sysobjects so, sysusers su
WHERE su.uid = @userid
AND su.uid = so.uid
AND so.name = @sourcename)

--if sourcename table does not exist, we exit the procedure
--with @change = 10.
IF @sourceid IS NULL
BEGIN
SET @change = 10
RETURN
END

--get the id of the modifyname table if it exists
SET @modifyid = (SELECT so.id 
FROM sysobjects so, sysusers su
WHERE su.uid = @userid
AND su.uid = so.uid
AND so.name = @modifyname)

--temp table to see what fields exist in the modifyname table
CREATE TABLE #fields (fieldorder INT, [field] VARCHAR(42), datatype VARCHAR(20), 
length INT, [tablea] BIT, [tableb] BIT, updated BIT)

--insert all of the fields from the sourcename table
INSERT INTO #fields (fieldorder, [field], datatype, length, [tablea], [tableb], updated)
SELECT colid, sc.name, st.name, sc.length, 1, NULL, 1
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype
AND sc.name <> 'QtrTable'

--mark the columns that already exist in modifyname table
UPDATE t
SET t.tableb = 1
FROM #fields t, syscolumns sc, systypes st
WHERE sc.id = @modifyid
AND sc.xtype = st.xtype
AND sc.name <> 'QtrTable'
AND sc.name = t.field

--determine if we need to create or modify the modifyname table
IF @modifyid IS NULL
  SET @strsql = 'CREATE TABLE ' + @strUser_nm + '.' + @modifyname + ' ('
ELSE
  SET @strsql = 'ALTER TABLE ' + @struser_nm + '.' + @modifyname + ' ADD '

--if no changes are necessary, exit the procedure.  @change is already = 0
IF (SELECT COUNT(*) FROM #fields WHERE tableb IS NULL) = 0
RETURN
ELSE
SET @change = 1

--PRINT @change
--PRINT 'within query'

--Loop through to add the needed fields to the create/alter statement.
WHILE (SELECT COUNT(*) FROM #fields WHERE tableb IS NULL) > 0
BEGIN

UPDATE #fields SET updated = 0

--get a field to add
SET @field = (SELECT TOP 1 field FROM #fields WHERE tableb IS NULL ORDER BY fieldorder)

--update the temp table to say the field now exists in the modifyname table
UPDATE #fields SET tableb = 1 WHERE field = @field

--get the details about the field
SELECT @datatype = datatype, @length = length FROM #fields WHERE field = @field

--the datatype determines the syntax needed
IF @datatype IN ('VARCHAR','CHAR')
SET @strsql = @strsql +'['+ @field +']'+ ' ' + @datatype + '(' + CONVERT(VARCHAR,@length) + '), '
ELSE
SET @strsql = @strsql +'['+ @field +']' + ' ' + @datatype + ', '

--end the loop to add the needed fields to the create/alter statement.
END

--get rid of the last comma 
SET @strsql = LEFT(@strsql,(LEN(@strsql)-1))

--close the parenthesis for the create statement
IF LEFT(@strsql,6) = 'CREATE'
SET @strsql = @strsql + ')'

--PRINT @strsql
--create/alter the table
EXEC (@strsql)


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO