Sunday, August 2, 2015

Script to create audit tables and DDL Triggers

USE [Test]




GO




/****** Object: Table [dbo].[audit_table_database_functions] Script Date: 2/6/2014 5:24:15 PM ******/

CREATE TABLE [dbo].[audit_table_database_functions](

[funtion_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




GO




/****** Object: Table [dbo].[audit_table_database_procedures] Script Date: 2/6/2014 5:24:31 PM ******/

CREATE TABLE [dbo].[audit_table_database_procedures](

[procedure_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



 
/****** Object: Table [dbo].[audit_table_database_tables] Script Date: 2/6/2014 5:24:40 PM ******/

CREATE TABLE [dbo].[audit_table_database_tables](

[table_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



 
/****** Object: Table [dbo].[audit_table_database_views] Script Date: 2/6/2014 5:24:49 PM ******/

CREATE TABLE [dbo].[audit_table_database_views](

[view_name] [varchar](200) NULL,

[user] [varchar](200) NULL,

[date_time] [datetime] NULL,

[activity] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




/*******************************Triggers******************************/

USE [Test]




GO




/****** Object: DdlTrigger [DDL_Funtion_Trigger] Script Date: 2/6/2014 5:25:07 PM ******/

CREATE TRIGGER [DDL_Funtion_Trigger]

ON DATABASE

FOR ALTER_FUNCTION, DROP_FUNCTION, CREATE_FUNCTION




AS
INSERT INTO audit_table_database_functions

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Funtion_Trigger] ON DATABASE




GO

USE [Test]




GO




/****** Object: DdlTrigger [DDL_Procedure_Trigger] Script Date: 2/6/2014 5:25:26 PM ******/

CREATE TRIGGER [DDL_Procedure_Trigger]

ON DATABASE

FOR ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_PROCEDURE




AS
INSERT INTO audit_table_database_procedures

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Procedure_Trigger] ON DATABASE




GO

USE [Test]




GO




/****** Object: DdlTrigger [DDL_View_Trigger] Script Date: 2/6/2014 5:25:37 PM ******/

CREATE TRIGGER [DDL_View_Trigger]

ON DATABASE

FOR ALTER_VIEW, DROP_VIEW, CREATE_VIEW




AS
INSERT INTO audit_table_database_views

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_View_Trigger] ON DATABASE




GO




/****** Object: DdlTrigger [DDL_Table_Trigger] Script Date: 2/6/2014 5:27:40 PM ******/

CREATE TRIGGER [DDL_Table_Trigger]

ON DATABASE

FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE




AS
INSERT INTO audit_table_database_tables

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')

,SUSER_SNAME()

,GETDATE()

,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')




GO

ENABLE TRIGGER [DDL_Table_Trigger] ON DATABASE




GO



 

 

 

 

 

 

 

 

 

 

Tuesday, March 24, 2015

Script to list all indexes and their columns to find the duplicate ones

SELECT




TableName
 
 
, IndexName

, IndexType

, [1] AS Column1

, [2] AS Column2

, [3] AS Column3

, [4] AS Column4

, [5] AS Column5

, [6] AS Column6

, [7] AS Column7

, [8] AS Column8

FROM




(
 
 
SELECT

OBJECT_NAME (ic.object_id) AS TableName

, COL_NAME(si.object_id, column_id) AS ColumnName

, si.[name] AS IndexName

, ic.key_ordinal

, si.[type_desc] AS IndexType

FROM sys.index_columns ic

INNER JOIN sys.indexes si

ON ic.index_id = si.index_id

AND ic.object_id = si.object_id

INNER JOIN sys.objects so

ON ic.object_id = so.object_id

WHERE so.is_ms_shipped <> 1

) PivotData




PIVOT

(
 
 


MIN (ColumnName)

FOR [key_ordinal] IN ([1], [2], [3], [4], [5], [6], [7], [8])




)
 
 
AS ColumnPivot

ORDER BY [TableName], [IndexName], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]


Ref: I refereed an online source and the script is very similar to the source but with some details removed to make it suit my needs