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



　

　

　

　

　

　

　

　

　

　

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 

Simple script to dump user tbale data into copy tables

This is a quick script to dump all user table data into tables of similar schema. I had to do this once to salvage data from a corrupt database. The script uses a TRY CATCH block to continue on if in the iterative process, it encounters a table that cannot be read
 (Remove comment mark from the PRINT statement before running)

DECLARE @table VARCHAR (200)

DECLARE @sql VARCHAR (MAX)
DECLARE fetch_table CURSOR FORWARD_ONLY
FOR
SELECT [name]
FROM sys.objects
WHERE [type] = 'U'
AND is_ms_shipped = 0

 

OPEN fetch_table

FETCH NEXT FROM fetch_table INTO @table

 

nextone:

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

PRINT 'try'+' '+@table

SET @sql = 'SELECT * FROM '+@table+' INTO '+@table+'_Copy'
RAISERROR ('Test', 11, 0);
--PRINT @sql
FETCH NEXT FROM fetch_table
INTO @table

END TRY

BEGIN CATCH

PRINT 'catch '+ @table

FETCH NEXT FROM fetch_table
INTO @table
GOTO nextone

END CATCH

 

END

 

CLOSE fetch_table

DEALLOCATE fetch_table