1: 'COPY D:\Temp\Temp\POC_DEV\Programmability\StoredProcedures\' +
2: ObjectName + '.sql D:\temp\temp\POC_DEV\1.2.8\StoredProcedures\'
3: + ObjectName + '.sql'
4:
5: FROM tbDataMeta_ChangeLog
6: where ObjectType = 'PROCEDURE'
7: AND EventDate>'2010-02-10'
8:
9:
10:
11:
12:
13:
14: USE [MY_DB]
15: GO
16:
17: IF EXISTS (SELECT * FROM dbo.sysobjects
18: WHERE id = OBJECT_ID(N'[DF_EventsLog_EventDate]') AND type = 'D')
19: BEGIN
20: ALTER TABLE [dbo].[DbObjectsChangeStore] DROP CONSTRAINT [DF_EventsLog_EventDate]
21: END
22:
23: GO
24:
25: USE [MY_DB]
26: GO
27:
28: /****** Object: Table [dbo].[DbObjectsChangeStore]
29: Script Date: 02/11/2010 17:58:00 ******/
30: IF EXISTS (SELECT * FROM sys.objects
31: WHERE object_id = OBJECT_ID(N'[dbo].[DbObjectsChangeStore]') AND type in (N'U'))
32: DROP TABLE [dbo].[DbObjectsChangeStore]
33: GO
34:
35: USE [MY_DB]
36: GO
37:
38: /****** Object: Table [dbo].[DbObjectsChangeStore]
39: Script Date: 02/11/2010 17:58:00 ******/
40: SET ANSI_NULLS ON
41: GO
42:
43: SET QUOTED_IDENTIFIER ON
44: GO
45:
46: CREATE TABLE [dbo].[DbObjectsChangeStore](
47: [LogId] [int] IDENTITY(1,1) NOT NULL,
48: [DatabaseName] [varchar](256) NOT NULL,
49: [EventType] [varchar](50) NOT NULL,
50: [ObjectName] [varchar](256) NOT NULL,
51: [ObjectType] [varchar](25) NOT NULL,
52: [SqlCommand] [varchar](max) NOT NULL,
53: [EventDate] [datetime] NOT NULL,
54: [LoginName] [varchar](256) NOT NULL
55: ) ON [PRIMARY]
56:
57: GO
58:
59: ALTER TABLE [dbo].[DbObjectsChangeStore]
60: ADD CONSTRAINT [DF_EventsLog_EventDate]
61: DEFAULT (getdate()) FOR [EventDate]
62: GO
63:
64:
65: IF EXISTS (SELECT * FROM sys.triggers
66: WHERE parent_class_desc = 'DATABASE' AND name = N'trig_BackUpDbObjects')
67: DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE
68:
69: GO
70:
71: USE [MY_DB]
72: GO
73:
74: /****** Object: DdlTrigger [trig_BackUpDbObjects]
75: Script Date: 02/11/2010 17:58:49 ******/
76: IF EXISTS (SELECT * FROM sys.triggers
77: WHERE parent_class_desc = 'DATABASE'
78: AND name = N'trig_BackUpDbObjects')
79: DROP TRIGGER [trig_BackUpDbObjects] ON DATABASE
80: GO
81:
82: USE [MY_DB]
83: GO
84:
85: /****** Object: DdlTrigger [trig_BackUpDbObjects] Script Date: 02/11/2010 17:58:49 ******/
86: SET ANSI_NULLS ON
87: GO
88:
89: SET QUOTED_IDENTIFIER ON
90: GO
91:
92:
93:
94: create trigger [trig_BackUpDbObjects]
95: on database
96: for create_procedure, alter_procedure, drop_procedure,
97: create_table, alter_table, drop_table,
98: create_function, alter_function, drop_function
99: as
100:
101: set nocount on
102:
103: declare @data xml
104: set @data = EVENTDATA()
105:
106: insert into [dbo].[DbObjectsChangeStore](databasename, eventtype,
107: objectname, objecttype, sqlcommand, loginname)
108: values(
109: @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), 110: @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 111: @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 112: @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 113: @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 114: @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 115: )
116:
117:
118:
119:
120: GO
121:
122: SET ANSI_NULLS OFF
123: GO
124:
125: SET QUOTED_IDENTIFIER OFF
126: GO
127:
128: DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE
129: GO
Posts
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!