· 7 years ago · Sep 18, 2018, 10:08 PM
1USE DW
2
3/* Drop table dbo.dim_audit */
4IF EXISTS ( SELECT *
5 FROM dbo.sysobjects
6 WHERE id = OBJECT_ID(N'[dbo].[dim_audit]')
7 AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
8 DROP TABLE dbo.[dim_audit]
9GO
10CREATE TABLE [dbo].[dim_audit](
11 [audit_key] [int] IDENTITY(1,1) NOT NULL,
12 [ParentAuditKey] [int] NOT NULL,
13 [TableName] [varchar](50) NOT NULL,
14 [PkgName] [varchar](100) NOT NULL,
15 [PkgGUID] [uniqueidentifier] NULL,
16 [PkgVersionGUID] [uniqueidentifier] NULL,
17 [PkgVersionMajor] [smallint] NULL,
18 [PkgVersionMinor] [smallint] NULL,
19 [ExecStartDT] [datetime] NOT NULL,
20 [ExecStopDT] [datetime] NULL,
21 [ExecutionInstanceGUID] [uniqueidentifier] NULL,
22 [ExtractRowCnt] [bigint] NULL,
23 [InsertRowCnt] [bigint] NULL,
24 [UpdateRowCnt] [bigint] NULL,
25 [ErrorRowCnt] [bigint] NULL,
26 [InferredMemberRowCnt] [bigint] NULL,
27 [TableInitialRowCnt] [bigint] NULL,
28 [TableFinalRowCnt] [bigint] NULL,
29 [TableMaxDateTime] [datetime] NULL,
30 [SuccessfulProcessingInd] [char](1) NOT NULL,
31 CONSTRAINT [PK_dbo.dim_audit] PRIMARY KEY CLUSTERED
32(
33 [audit_key] ASC
34)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35) ON [PRIMARY]
36SET IDENTITY_INSERT [dbo].[dim_audit] ON
37
38INSERT [dbo].[dim_audit] ([audit_key], [ParentAuditKey], [TableName], [PkgName], [PkgGUID], [PkgVersionGUID], [PkgVersionMajor], [PkgVersionMinor], [ExecStartDT], [ExecStopDT], [ExecutionInstanceGUID], [ExtractRowCnt], [InsertRowCnt], [UpdateRowCnt], [ErrorRowCnt],[InferredMemberRowCnt], [TableInitialRowCnt], [TableFinalRowCnt], [TableMaxDateTime], [SuccessfulProcessingInd]) VALUES (-1, -1, N'Not Applicable', N'Initial Load', NULL, NULL, 0, 0, getdate(), getdate(), NULL, 0, 0, 0, 0, 0, 0, 0, getdate(), N' ')
39SET IDENTITY_INSERT [dbo].[dim_audit] OFF
40ALTER TABLE [dbo].[dim_audit] ADD DEFAULT ('Unknown') FOR [TableName]
41GO
42ALTER TABLE [dbo].[dim_audit] ADD DEFAULT ('Unknown') FOR [PkgName]
43GO
44ALTER TABLE [dbo].[dim_audit] ADD DEFAULT (getdate()) FOR [ExecStartDT]
45GO
46ALTER TABLE [dbo].[dim_audit] ADD DEFAULT ('N') FOR [SuccessfulProcessingInd]
47GO
48
49ALTER TABLE [dbo].[dim_audit] WITH CHECK ADD CONSTRAINT [FK_dbo_dim_audit_ParentAuditKey] FOREIGN KEY([ParentAuditKey])
50REFERENCES [dbo].[dim_audit] ([audit_key])
51GO
52ALTER TABLE [dbo].[dim_audit] CHECK CONSTRAINT [FK_dbo_dim_audit_ParentAuditKey]
53GO