-
Notifications
You must be signed in to change notification settings - Fork 0
/
Schema_Change.sql
159 lines (142 loc) · 5.61 KB
/
Schema_Change.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
/*
disable trigger [tr_Schema_Change] on database;
-- enable trigger [tr_Schema_Change] on database;
drop trigger if exists [tr_Schema_Change] on database;
drop view if exists [Tools].[vw_Schema_Change];
-- DANGER: do not drop the Tools.Schema_Change tables. Loss of data!
*/
-- disable the trigger so that it doesn't fire while running this script.
if exists (select * from sys.triggers where name = 'tr_Schema_Change' and parent_class_desc = 'DATABASE')
disable trigger [tr_Schema_Change] on database;
if SCHEMA_ID('Tools') is null exec ('create schema [Tools] authorization [dbo]');
if OBJECT_ID('[Tools].[Schema_Change_User]') is null
create table [Tools].[Schema_Change_User] (
Schema_Change_User_ID int not null identity constraint PK_Tools_Schema_Change_User primary key,
Login_Name sysname null,
[User_Name] sysname null,
constraint UX_Tools_Schema_Change_User unique (Login_Name, [User_Name])
);
if OBJECT_ID('[Tools].[Schema_Change_Object]') is null
create table [Tools].[Schema_Change_Object] (
Schema_Change_Object_ID int not null identity constraint PK_Tools_Schema_Change_Object primary key,
[Schema_Name] sysname null,
[Object_Name] sysname null,
constraint UX_Tools_Schema_Change_Object unique ([Schema_Name], [Object_Name])
);
if OBJECT_ID('[Tools].[Schema_Change]') is null
create table [Tools].[Schema_Change] (
Schema_Change_ID int not null identity
constraint PK_Tools_Schema_Change primary key,
Trigger_Event_Type int null,
Post_Time datetime null, -- local
Schema_Change_User_ID int null
constraint FK_Tools_Schema_Change_User_ID
references [Tools].[Schema_Change_User] (Schema_Change_User_ID),
Schema_Change_Object_ID int null
constraint FK_Tools_Schema_Change_Object_ID
references [Tools].[Schema_Change_Object] (Schema_Change_Object_ID),
Alter_Table_Action_List xml null,
Command_Text nvarchar(MAX) null
);
go
-- ----------------------------------------------------------------
-- Called by the [Tools].[vw_Schema_Validation] view.
-- SELECT TOP 100 * FROM [Tools].[vw_Schema_Change] ORDER BY 1 DESC
create or alter view [Tools].[vw_Schema_Change] as
SELECT
e.Schema_Change_ID,
tet.[type_name],
e.Post_Time,
u.Login_Name,
u.[User_Name],
o.[Schema_Name] + '.' + o.[Object_Name] as [Object_Name],
e.Command_Text,
e.Alter_Table_Action_List
FROM [Tools].[Schema_Change] e
left join sys.trigger_event_types tet on e.Trigger_Event_Type = tet.[type]
left join [Tools].[Schema_Change_User] u on e.Schema_Change_User_ID = u.Schema_Change_User_ID
left join [Tools].[Schema_Change_Object] o on e.Schema_Change_Object_ID = o.Schema_Change_Object_ID
go
create or alter proc [Tools].[P_Find_Schema_Change] @Saught varchar(MAX) as
SELECT
Post_Time,
[type_name],
Login_Name,
[Object_Name],
Command_Text,
Alter_Table_Action_List
FROM [Tools].[vw_Schema_Change]
where Command_Text like '%' + REPLACE(REPLACE(@Saught, '[', '[[]'), '_', '[_]') + '%'
or [type_name] = @Saught
or Login_Name = @Saught
or [Object_Name] = @Saught
order by Schema_Change_ID desc;
go
-- ----------------------------------------------------------------
-- This trigger fires for any change to the schema in this database.
-- It does 2 things:
-- 1 - Records the schema change
-- 2 - Run the validation proc if it exists
create or alter trigger [tr_Schema_Change] on database after DDL_DATABASE_LEVEL_EVENTS as
set nocount on;
declare
@Event_Data XML = EVENTDATA(),
@Event_Type_Name nvarchar(64),
@Post_Time datetime,
@Login_Name sysname,
@User_Name sysname,
@Schema_Name sysname,
@Object_Name sysname,
@Alter_Table_Action_List xml,
@Command_Text nvarchar(MAX);
select
@Event_Type_Name = c.value('EventType[1]', 'nvarchar(64)'),
@Post_Time = c.value('PostTime[1]', 'datetime'),
@Login_Name = c.value('LoginName[1]', 'sysname'),
@User_Name = c.value('UserName[1]', 'sysname'),
@Schema_Name = c.value('SchemaName[1]', 'sysname'),
@Object_Name = c.value('ObjectName[1]', 'sysname'),
@Alter_Table_Action_List = c.query('AlterTableActionList/*'),
@Command_Text = c.value('(TSQLCommand/CommandText)[1]', 'nvarchar(MAX)')
from @Event_Data.nodes('EVENT_INSTANCE') t(c);
insert [Tools].[Schema_Change_User] (Login_Name, [User_Name])
select @Login_Name, @User_Name
except
select Login_Name, [User_Name] from [Tools].[Schema_Change_User] WITH (HOLDLOCK, UPDLOCK);
insert [Tools].[Schema_Change_Object] ([Schema_Name], [Object_Name])
select @Schema_Name, @Object_Name
except
select [Schema_Name], [Object_Name] from [Tools].[Schema_Change_Object] WITH (HOLDLOCK, UPDLOCK);
insert [Tools].[Schema_Change] (
Trigger_Event_Type,
Post_Time,
Schema_Change_User_ID,
Schema_Change_Object_ID,
Alter_Table_Action_List,
Command_Text
)
select
t.[type],
@Post_Time,
u.Schema_Change_User_ID,
o.Schema_Change_Object_ID,
@Alter_Table_Action_List,
@Command_Text
from sys.trigger_event_types t
cross join [Tools].[Schema_Change_User] u
cross join [Tools].[Schema_Change_Object] o
where t.[type_name] = @Event_Type_Name
and u.Login_Name = @Login_Name and u.[User_Name] = @User_Name
and o.[Schema_Name] = @Schema_Name and o.[Object_Name] = @Object_Name;
declare @Schema_Change_ID int = SCOPE_IDENTITY();
-- ====================== --
-- ===== VALIDATION ===== --
declare @Proc_Name nvarchar(MAX) = '[Tools].[p_Validate_Schema]';
declare @Instruction nvarchar(MAX) = CONCAT(@Proc_Name, ' ', @Schema_Change_ID);
if OBJECT_ID(@Proc_Name) is not null exec (@Instruction);
-- ====================== --
-- ====================== --
go
if exists (select * from sys.triggers where name = 'tr_Schema_Change' and parent_class_desc = 'DATABASE')
enable trigger [tr_Schema_Change] on database;
go