Procedure: [AdventureWorks2016CTP3].[dbo].[AUDIT_prc_DeleteArchitecture]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Type   P 
 Encrypted   
 ID   1419868125 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@RemoveServer   bit 1 Input  

Collapse image Objects that [dbo].[AUDIT_prc_DeleteArchitecture] depends on


Object name Object type Dep level
[dbo].[AUDIT_prc_AddAuditUndoItem] Procedure 1
[dbo].[AUDIT_prc_AggregateReport] Procedure 1
[dbo].[AUDIT_prc_Analyze] Procedure 1
[dbo].[AUDIT_prc_CheckAuditUndo] Procedure 1
[dbo].[AUDIT_prc_CommitUndo] Procedure 1
[dbo].[AUDIT_prc_CreateAuditUndoReport] Procedure 1
[dbo].[AUDIT_prc_DDLReport] Procedure 1
[dbo].[AUDIT_prc_ExecUndo] Procedure 1
[dbo].[AUDIT_prc_GetAuditUndoReport] Procedure 1
[dbo].[AUDIT_prc_Purge_AUDIT_LOG] Procedure 1
[dbo].[AUDIT_prc_ReportingAddFilterValue] Procedure 1
[dbo].[AUDIT_prc_ReportingEnd] Procedure 1
[dbo].[AUDIT_prc_ReportingStart] Procedure 1
[dbo].[AUDIT_prc_RollbackUndo] Procedure 1
[dbo].[AUDIT_prc_RunUndo] Procedure 1
[dbo].[AUDIT_prc_StandardReport] Procedure 1
[dbo].[AUDIT_prc_UndoAddTriggersCheck] Procedure 1
[dbo].[AUDIT_prc_UndoCheck] Procedure 1
[dbo].[AUDIT_prc_UndoGenerateCommand] Procedure 1
[dbo].[AUDIT_LOG_DATA] Table 1
[dbo].[AUDIT_LOG_DDL] Table 1
[dbo].[AUDIT_LOG_TRANSACTIONS] Table 1
[dbo].[tr_ddl_AUDIT] Trigger 1
[dbo].[AUDIT_UNDO] View 1
[dbo].[AUDIT_VIEW] View 1
Total 25 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.AUDIT_prc_DeleteArchitecture
    @RemoveServer bit 
AS
declare @cmptlvl int
-- Delete Audit Tables
IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AUDIT_LOG_DATA]') AND type in (N'U')) DROP TABLE [dbo].[AUDIT_LOG_DATA]
IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AUDIT_LOG_TRANSACTIONS]') AND type in (N'U')) DROP TABLE [dbo].[AUDIT_LOG_TRANSACTIONS]
IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AUDIT_LOG_DDL]') AND type in (N'U')) DROP TABLE [dbo].[AUDIT_LOG_DDL]
-- Delete DDL trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'tr_ddl_AUDIT' AND type in (N'TR'))
BEGIN
    DROP TRIGGER tr_ddl_AUDIT ON DATABASE
END
Select @cmptlvl = t1.cmptlevel 
from master.dbo.sysdatabases t1
where t1.[name]=DB_NAME()
IF @cmptlvl > 70
BEGIN
declare 
@fn_sql nvarchar(4000)
set @fn_sql = 'DROP FUNCTION dbo.AUDIT_fn_HexToStr'
IF OBJECTPROPERTY(OBJECT_ID('dbo.AUDIT_fn_HexToStr'), 'IsScalarFunction') IS NOT NULL 
    exec sp_executesql @fn_sql 
set @fn_sql = 'DROP FUNCTION dbo.AUDIT_fn_SqlVariantToString'
IF OBJECTPROPERTY(OBJECT_ID('dbo.AUDIT_fn_SqlVariantToString'), 'IsScalarFunction') IS NOT NULL
    exec sp_executesql @fn_sql
END
-- Delete Audit View
IF OBJECT_ID('dbo.AUDIT_VIEW', 'V') IS NOT NULL DROP VIEW dbo.AUDIT_VIEW
IF OBJECT_ID('dbo.AUDIT_UNDO', 'V') IS NOT NULL DROP VIEW dbo.AUDIT_UNDO
-- Delete Common Reporting functions
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingStart]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AUDIT_prc_ReportingStart]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingEnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AUDIT_prc_ReportingEnd]
-- Delete Aggregate Report
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingStart]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[AUDIT_prc_ReportingStart]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingAddFilterValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[AUDIT_prc_ReportingAddFilterValue]
IF OBJECT_ID('dbo.AUDIT_prc_AggregateReport','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_AggregateReport
-- Delete Standard Report
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingStart]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[AUDIT_prc_ReportingStart]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUDIT_prc_ReportingAddFilterValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[AUDIT_prc_ReportingAddFilterValue]
IF OBJECT_ID('dbo.AUDIT_prc_StandardReport','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_StandardReport
-- Delete DDL Report
IF OBJECT_ID('dbo.AUDIT_prc_DDLReport','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_DDLReport
-- Delete Purge Data Sproc
IF OBJECT_ID('dbo.AUDIT_prc_Purge_AUDIT_LOG','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_Purge_AUDIT_LOG
-- Delete Undo Procedures
IF OBJECT_ID('dbo.AUDIT_prc_AddAuditUndoItem','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_AddAuditUndoItem
IF OBJECT_ID('dbo.AUDIT_prc_CheckAuditUndo','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_CheckAuditUndo
IF OBJECT_ID('dbo.AUDIT_prc_CommitUndo','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_CommitUndo
IF OBJECT_ID('dbo.AUDIT_prc_RollbackUndo','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_RollbackUndo
IF OBJECT_ID('dbo.AUDIT_prc_UndoGenerateCommand','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_UndoGenerateCommand]
IF OBJECT_ID('dbo.AUDIT_prc_UndoCheck','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_UndoCheck]
IF OBJECT_ID('dbo.AUDIT_prc_GetAuditUndoReport','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_GetAuditUndoReport]
IF OBJECT_ID('dbo.AUDIT_prc_RunUndo','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_RunUndo]
IF OBJECT_ID('dbo.AUDIT_prc_ExecUndo','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_ExecUndo]
IF OBJECT_ID('dbo.AUDIT_prc_CreateAuditUndoReport','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_CreateAuditUndoReport]
IF OBJECT_ID('dbo.AUDIT_prc_UndoAddTriggersCheck','P') IS NOT NULL DROP PROCEDURE [dbo].[AUDIT_prc_UndoAddTriggersCheck]
-- Delete Analyze Procedures
IF OBJECT_ID('dbo.AUDIT_prc_Analyze','P') IS NOT NULL DROP PROCEDURE dbo.AUDIT_prc_Analyze
-- Delete Audit Triggers
DECLARE @trname nvarchar(261)
DECLARE @usname nvarchar(261)
DECLARE @sql nvarchar(4000)
create table #names(username nvarchar(2000), name nvarchar(2000));
IF @cmptlvl < 90
set @sql='insert into #names select u.name, o.name 
from sysobjects o, syscomments c, sysusers u 
  where o.xtype = ''TR'' 
  and o.id = c.id 
  and c.colid = 1 
  and u.uid=o.uid 
  and c.text like ''%<TAG>SQLAUDIT GENERATED - DO NOT REMOVE</TAG>%'''
else
set @sql='insert into #names select u.name, o.name 
from sysobjects o, syscomments c, sys.schemas u 
  where o.xtype = ''TR'' 
  and o.id = c.id 
  and c.colid = 1 
  and u.schema_id=o.uid 
  and c.text like ''%<TAG>SQLAUDIT GENERATED - DO NOT REMOVE</TAG>%'''
EXEC sp_executesql @sql
DECLARE CRTR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  SELECT distinct username, name
  FROM #names
OPEN CRTR
FETCH CRTR INTO @usname, @trname
WHILE @@FETCH_STATUS=0
BEGIN
   SET @sql=N'DROP TRIGGER ['+@usname+'].['+@trname+']'
   print @sql
   EXEC sp_executesql @sql
   FETCH CRTR INTO @usname, @trname
END
CLOSE CRTR DEALLOCATE CRTR
drop table #names;
GO

Collapse image See also


List of procedures