Bobs Script Vault

Sessions: 98	The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
LifeAsBob		Friday, April 19, 2024

lifeasbob.com

Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

Branson Vacation 2007

bob Links

Microsoft redrawing ...

The selling of bad beer is a crime against Christian love. - Thirteenth-century law from the city of Augsburg

Bob Code 5101

Things

Script Vault:

280 scripts loaded.

Script Name Code Description

Script

USE [TAX_ODS_MISC_TS12]
GO

if not exists(select * from sys.schemas where [name] = 'dba')
begin
 exec sp_executesql 'create schema dba'
end
go

/****** Object:  Table [dba].[DatabaseLog]    Script Date: 4/23/2018 3:03:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dba].[DatabaseLog](
	[PostTime] [datetime] NULL,
	[DatabaseUser] [varchar](100) NULL,
	[hostname] [varchar](100) NULL,
	[Event] [varchar](100) NULL,
	[Schema] [varchar](100) NULL,
	[Object] [varchar](100) NULL,
	[TSQL] [nvarchar](2000) NULL,
	[XmlEvent] [xml] NULL,
	[DatabaseName] [varchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

/****** Object:  DdlTrigger [ddlDatabaseTrigger_DatabaseChangeLog]    Script Date: 4/23/2018 3:02:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddlDatabaseTrigger_DatabaseChangeLog]
ON DATABASE
WITH EXECUTE AS SELF
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
BEGIN
   SET NOCOUNT ON;
--SET QUOTED_IDENTIFIER off
 
    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @LoginName sysname;
    DECLARE @SPID sysname;
    DECLARE @hostname varchar(100);
 
    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') ;
    SET @LoginName =@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') ;
    SET @SPID =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'sysname');
      
SET QUOTED_IDENTIFIER on
SELECT @hostname=hostname FROM master.dbo.sysprocesses WHERE spid=@SPID
 
 
    INSERT [dba].[DatabaseLog]
        (
        [PostTime],
        [DatabaseUser],
        [Event],
        [Schema],
        [Object],
        [XmlEvent],
              [hostname]
        )
    VALUES
        (
        GETDATE(),
        @LoginName,
        @eventType,
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data,
       @hostname
        );
 
--SET NOCOUNT OFF
 
END;

ENABLE TRIGGER [ddlDatabaseTrigger_DatabaseChangeLog] ON DATABASE
GO

Notes:

Update by:	A698060
Updated On:	4/23/2018 4:14:00 PM
Created by:	A698060
Created On:	4/23/2018 4:14:00 PM

Script Name:

Think ! The text boxes below only works if your particular SQL Server is available via where this website is hosted / on the internet, so this is really code that is for me !; though I am working to update it to run either server side or client side via a check box, this will be a nice future enhancement (think AJAX).
Integrated Authentication	Time Out: Wrap cell contents in results Display Messages (Print)	Run For a specific database>