监控-补充

原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！

【基础表】

USE [DBA_Monitor]
GO
/****** 对象:  Table [dbo].[Login_PasswordSetLog]    脚本日期: 02/08/2017 16:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Login_PasswordSetLog](
    [LoginsID] [bigint] NULL,
    [PasswordSetTime] [datetime] NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_PasswordSetLog_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_PasswordSetLog', @level2type=N'COLUMN',@level2name=N'LoginsID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码更改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_PasswordSetLog', @level2type=N'COLUMN',@level2name=N'PasswordSetTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_PasswordSetLog', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码变更日志表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_PasswordSetLog'
GO
/****** 对象:  Table [dbo].[Info_JobHistory]    脚本日期: 02/08/2017 15:59:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_JobHistory](
    [InstanceID] [int] NOT NULL,
    [JobName] [varchar](128) NOT NULL,
    [OwnName] [varchar](64) NOT NULL,
    [Step_ID] [int] NOT NULL,
    [Step_Name] [varchar](128) NOT NULL,
    [MessageInfo] [varchar](max) NULL,
    [Run_Status] [int] NOT NULL,
    [Run_DateTime] [datetime] NOT NULL,
    [Run_Duration] [varchar](8) NOT NULL,
    [Prev_Run_Duration] [varchar](8) NULL,
    [Growth(%)] [varchar](8) NULL,
    [Flag] [tinyint] NOT NULL,
    [InsertTime] [datetime] NOT NULL CONSTRAINT [DF_JobHistory_InsertTime]  DEFAULT (getdate()),
    [ETLTime] [datetime] NULL CONSTRAINT [DF_JobHistory_ETLTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_JobHistory] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ETLTime] ON [dbo].[Info_JobHistory] 
(
    [ETLTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_JobHistory] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Login_LoginsInfo]    脚本日期: 02/08/2017 16:00:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Login_LoginsInfo](
    [LoginsID] [bigint] NULL,
    [InstanceID] [int] NOT NULL,
    [LoginName] [nvarchar](64) NOT NULL,
    [CreateDate] [datetime] NULL,
    [PasswordLastSetTime] [datetime] NULL,
    [IsSystemUser] [bit] NULL,
    [IsDisabled] [bit] NULL,
    [IsPolicyChecked] [bit] NULL,
    [IsExpirationChecked] [bit] NULL,
    [IsLocked] [bit] NULL,
    [DaysUntilExpiration] [int] NULL,
    [PossibleDisableTime] [datetime] NULL,
    [UpdateTime] [datetime] NULL CONSTRAINT [DF_LoginsInfo_UpdateTime]  DEFAULT (getdate()),
    [IsDeleted] [bit] NULL CONSTRAINT [DF_LoginsInfo_IsDeleted]  DEFAULT ((0)),
 CONSTRAINT [PK_LOGIN_LOGINSINFO] PRIMARY KEY CLUSTERED 
(
    [InstanceID] ASC,
    [LoginName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'LoginsID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'LoginName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上次修改密码时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'PasswordLastSetTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否系统用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsSystemUser'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否禁用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsDisabled'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否强制密码策略' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsPolicyChecked'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否强制密码过期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsExpirationChecked'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否登录已锁定' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsLocked'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码到期剩余天数(为空则不过期)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'DaysUntilExpiration'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参考禁用时间(为空则在用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'PossibleDisableTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'UpdateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否已删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo', @level2type=N'COLUMN',@level2name=N'IsDeleted'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库登录名信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Login_LoginsInfo'
GO
/****** 对象:  Table [dbo].[Info_TableSpace]    脚本日期: 02/08/2017 16:00:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_TableSpace](
    [InstanceID] [int] NOT NULL,
    [DBname] [varchar](32) NOT NULL,
    [TableName] [varchar](128) NOT NULL,
    [TableRows] [int] NULL CONSTRAINT [DF_TableRowsLog_TableRows]  DEFAULT ((0)),
    [Reserved] [int] NULL CONSTRAINT [DF_TableRowsLog_Reserved]  DEFAULT ((0)),
    [DataSize] [int] NULL CONSTRAINT [DF_TableRowsLog_DataSize]  DEFAULT ((0)),
    [IndexSize] [int] NULL CONSTRAINT [DF_TableRowsLog_IndexSize]  DEFAULT ((0)),
    [Unused] [int] NULL CONSTRAINT [DF_TableRowsLog_Unused]  DEFAULT ((0)),
    [InsertTime] [datetime] NULL CONSTRAINT [DF_TableRowsLog_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_TableSpace] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_TableSpace] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_Instance]    脚本日期: 02/08/2017 15:59:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_Instance](
    [InstanceID] [int] NOT NULL,
    [Serverid] [int] NOT NULL,
    [HostName] [varchar](32) NOT NULL,
    [InstanceName] [varchar](32) NOT NULL,
    [CreateTime] [datetime] NULL,
    [Edition] [varchar](32) NULL,
    [ProductVersion] [varchar](64) NULL,
    [ProductLevel] [varchar](16) NULL,
    [Language] [varchar](16) NULL,
    [Collation_Name] [varchar](32) NULL,
    [UserMaxConnections] [int] NULL,
    [BackupOption] [varchar](32) NULL,
    [RemoteTimeOut] [int] NULL,
    [MinMemory] [int] NULL,
    [MaxMemory] [int] NULL,
    [NetPort] [int] NULL,
    [InsertTime] [datetime] NULL,
 CONSTRAINT [PK_INFO_INSTANCE] PRIMARY KEY CLUSTERED 
(
    [InstanceID] ASC,
    [Serverid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'Serverid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'HostName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'InstanceName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'CreateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例版本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'Edition'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ProductVersion' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'ProductVersion'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SP版本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'ProductLevel'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'默认语言' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'Language'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序规则' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'Collation_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大连接数设置' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'UserMaxConnections'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备份选项' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'BackupOption'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'远程查询超时值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'RemoteTimeOut'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例分配最小内存' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'MinMemory'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例分配最大内存' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'MaxMemory'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例端口号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'NetPort'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例配置信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Instance'
GO
/****** 对象:  Table [dbo].[Info_Hardware]    脚本日期: 02/08/2017 15:59:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_Hardware](
    [ServerID] [int] NOT NULL,
    [DeviceID] [varchar](32) NOT NULL,
    [HostName] [varchar](64) NOT NULL,
    [ServerType] [varchar](128) NULL,
    [CpuInfo] [varchar](128) NULL,
    [MemorySize] [int] NULL,
    [HardDiskSize_Sum] [int] NULL,
    [HardDiskType] [varchar](128) NULL,
    [RaidInfo] [varchar](32) NULL,
    [HardDiskNum] [int] NULL,
    [MAC_1] [varchar](32) NULL,
    [MAC_2] [varchar](32) NULL,
    [MAC_3] [varchar](32) NULL,
    [BuyTime] [datetime] NULL,
    [Buyer] [varchar](32) NULL,
    [MachineRoomID] [int] NULL,
    [OS_Name] [varchar](64) NULL,
    [OS_Version] [varchar](64) NULL,
    [OS_SetupTime] [datetime] NULL,
    [IntranetIP] [varchar](15) NULL,
    [InternetIP] [varchar](15) NULL,
    [SpareIP] [varchar](15) NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_Hardware_RecordTime]  DEFAULT (getdate()),
    [LastRestartTime] [datetime] NULL,
 CONSTRAINT [PK_INFO_HARDWARE] PRIMARY KEY CLUSTERED 
(
    [ServerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'ServerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'资产编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'DeviceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'HostName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'ServerType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CPU信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'CpuInfo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内存信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'MemorySize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'硬盘总大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'HardDiskSize_Sum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'硬盘型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'HardDiskType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'RAID情况' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'RaidInfo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物理硬盘数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'HardDiskNum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MAC地址1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'MAC_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MAC地址2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'MAC_2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MAC地址3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'MAC_3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采购时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'BuyTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采购人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'Buyer'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在机房ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'MachineRoomID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作系统名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'OS_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作系统版本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'OS_Version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作系统安装时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'OS_SetupTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内网IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'IntranetIP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外网IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'InternetIP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备用IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'SpareIP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登记时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上次重启时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware', @level2type=N'COLUMN',@level2name=N'LastRestartTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'硬件信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Hardware'
GO
/****** 对象:  Table [dbo].[Info_DiskSpace]    脚本日期: 02/08/2017 15:59:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_DiskSpace](
    [ServerID] [int] NOT NULL,
    [InstanceNum] [int] NULL CONSTRAINT [DF_DiskSpace_InstanceNum]  DEFAULT ((1)),
    [InstanceID] [int] NULL,
    [DriveLetter] [varchar](8) NOT NULL,
    [TotalSize] [int] NULL,
    [UnUsedSize] [int] NULL,
    [AvailableRate] [varchar](8) NULL,
    [IsTempdb] [varchar](3) NULL,
    [GrowthNeedSpace] [int] NULL,
    [BackupNeedSpaceToday] [int] NULL,
    [BackupNeedSpaceOneDay] [int] NULL,
    [AllowGrowthTimes] [decimal](10, 2) NULL,
    [AllowBackupTimes] [decimal](10, 2) NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_DiskSpace_InsertTime]  DEFAULT (getdate()),
    [TotalSizeTime] [datetime] NULL CONSTRAINT [DF_DiskSpace_TotalSizeTime]  DEFAULT (getdate()),
 CONSTRAINT [PK_Info_DiskSpace] PRIMARY KEY CLUSTERED 
(
    [ServerID] ASC,
    [DriveLetter] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'ServerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'InstanceNum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'磁盘分区盘符' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'DriveLetter'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分区大小(MB)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'TotalSize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分区剩余大小(MB)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'UnUsedSize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分区可用率' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'AvailableRate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否是临时数据库tempdb存放分区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'IsTempdb'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库增长空间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'GrowthNeedSpace'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'在当天剩余的时间里，数据库备份所需的磁盘空间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'BackupNeedSpaceToday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库一天备份所需的磁盘空间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'BackupNeedSpaceOneDay'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'允许数据库增长次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'AllowGrowthTimes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'允许备份次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'AllowBackupTimes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'逻辑磁盘总大小获取时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace', @level2type=N'COLUMN',@level2name=N'TotalSizeTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'磁盘信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DiskSpace'
GO
/****** 对象:  Table [dbo].[Info_ErrorLog]    脚本日期: 02/08/2017 15:59:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_ErrorLog](
    [InstanceID] [int] NOT NULL,
    [LogDate] [datetime] NULL,
    [ProcessInfo] [varchar](64) NULL,
    [LogText] [varchar](1024) NULL,
    [Flag] [tinyint] NOT NULL,
    [InsertTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_InsertTime]  DEFAULT (getdate()),
    [ETLTime] [datetime] NULL CONSTRAINT [DF_ErrorLog_ETLTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_ErrorLog] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ETLTime] ON [dbo].[Info_ErrorLog] 
(
    [ETLTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_ErrorLog] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_CpuUseLog]    脚本日期: 02/08/2017 15:59:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_CpuUseLog](
    [InstanceID] [int] NOT NULL,
    [SQLServerProcessCPUUtilization] [int] NOT NULL,
    [SystemIdleProcess] [int] NOT NULL,
    [OtherProcessCPUUtilization] [int] NOT NULL,
    [EventTime] [datetime] NOT NULL,
    [ETLTime] [datetime] NULL CONSTRAINT [DF_CpuUseLog_ETLTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_EventTime] ON [dbo].[Info_CpuUseLog] 
(
    [EventTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ETLTime] ON [dbo].[Info_CpuUseLog] 
(
    [ETLTime] ASC
)
INCLUDE ( [InstanceID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_CpuUseLog] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_Server]    脚本日期: 02/08/2017 15:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_Server](
    [ServerID] [int] NOT NULL,
    [HostName] [varchar](64) NOT NULL,
    [IntranetIP] [varchar](15) NULL,
    [ShortIP] [varchar](3) NULL,
    [Plat] [varchar](32) NULL,
 CONSTRAINT [PK_INFO_SERVER] PRIMARY KEY CLUSTERED 
(
    [ServerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[SSIS_Configuration]    脚本日期: 02/08/2017 16:00:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS_Configuration](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServerID] [int] NOT NULL,
    [InstanceID] [int] NULL,
    [SourceServerName] [varchar](64) NULL,
    [SourceConnection] [varchar](128) NULL,
    [IsRemote] [bit] NULL,
    [IsEnabled] [bit] NULL,
 CONSTRAINT [PK_SSIS_Configuration] PRIMARY KEY CLUSTERED 
(
    [ServerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[SSIS_PackageErrorLog]    脚本日期: 02/08/2017 16:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS_PackageErrorLog](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SourceServerName] [varchar](20) NULL,
    [PackageName] [nvarchar](20) NULL,
    [TaskName] [nvarchar](64) NULL,
    [ErrorDescription] [nvarchar](256) NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_PackageRunErrorLog_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_Blocked]    脚本日期: 02/08/2017 15:58:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_Blocked](
    [InstanceID] [int] NOT NULL,
    [BlockedSessionId] [smallint] NULL,
    [DatabaseName] [sysname] NOT NULL,
    [WaitType] [nvarchar](60) NULL,
    [WaitResource] [nvarchar](256) NULL,
    [BlockedTime] [datetime] NULL,
    [WaitDuration_ms] [bigint] NULL,
    [BlockedClientAddress] [varchar](48) NULL,
    [BlockedStmt] [nvarchar](max) NULL,
    [BlockedBatch] [nvarchar](max) NULL,
    [BlockingSessionId] [smallint] NULL,
    [BlockingIsolationLevel] [tinyint] NULL,
    [BlockingHoldResource] [nvarchar](max) NULL,
    [BlockingStmt] [nvarchar](max) NULL,
    [BlockingBatch] [nvarchar](max) NULL,
    [BlockingClientAddress] [varchar](48) NULL,
    [BlockingProgramName] [nchar](128) NULL,
    [BlockingLoginame] [nchar](128) NULL,
    [InsertTime] [datetime] NULL,
    [ETLTime] [datetime] NULL CONSTRAINT [DF_Blocked_ETLTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_Blocked] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_Blocked] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_DDLChangeLog]    脚本日期: 02/08/2017 15:59:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_DDLChangeLog](
    [InstanceID] [int] NOT NULL,
    [DBName] [varchar](64) NULL,
    [EventType] [varchar](100) NULL,
    [ObjectName] [varchar](100) NULL,
    [ObjectType] [varchar](100) NULL,
    [TSQL] [varchar](max) NULL,
    [SQLType] [tinyint] NULL,
    [CurrentUser] [varchar](50) NULL CONSTRAINT [DF_DDLChangeLog_CurrentUser]  DEFAULT (CONVERT([varchar](50),user_name(),(0))),
    [LoginName] [varchar](50) NULL CONSTRAINT [DF_DDLChangeLog_LoginName]  DEFAULT (CONVERT([varchar](50),suser_sname(),(0))),
    [Username] [varchar](50) NULL CONSTRAINT [DF_DDLChangeLog_Username]  DEFAULT (CONVERT([varchar](50),original_login(),(0))),
    [HostName] [varchar](50) NULL CONSTRAINT [DF_DDLChangeLog_HostName]  DEFAULT (CONVERT([varchar](50),host_name(),(0))),
    [InsertTime] [datetime] NULL CONSTRAINT [DF_DDLChangeLog_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_DDLChangeLog] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'EventType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作对象名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'ObjectName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'ObjectType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行SQL' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'TSQL'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SQL类型(1while、2Cursor、3Tran)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'SQLType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'CurrentUser'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'LoginName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'Username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本机名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'HostName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DDLChangeLog', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
/****** 对象:  Table [dbo].[ExecErrorLog]    脚本日期: 02/08/2017 15:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExecErrorLog](
    [InstanceID] [int] NULL,
    [Error_Procname] [varchar](50) NULL,
    [Error_Numbers] [int] NULL,
    [Error_Messages] [varchar](max) NULL,
    [Error_Serverity] [int] NULL,
    [Error_States] [int] NULL,
    [Error_Lines] [int] NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_LockErro_CreateTime]  DEFAULT (getdate()),
    [ETLTime] [datetime] NULL CONSTRAINT [DF_ExecErrorLog_ETLTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
/****** 对象:  Table [dbo].[Info_Database]    脚本日期: 02/08/2017 15:59:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_Database](
    [InstanceID] [int] NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [DatabaseName] [varchar](32) NOT NULL,
    [DatabaseSize] [int] NOT NULL,
    [StateDesc] [varchar](32) NOT NULL,
    [LogReuseWaitDesc] [varchar](32) NULL,
    [CreateDate] [datetime] NULL,
    [OwnerName] [varchar](32) NULL,
    [CollationName] [varchar](32) NULL,
    [RecoveryModelDesc] [varchar](16) NULL,
    [CompatibilityLevel] [tinyint] NULL,
    [UserAccessDesc] [varchar](16) NULL,
    [ReadOnlyDesc] [varchar](16) NULL,
    [PageVerifyOptionDesc] [varchar](16) NULL,
    [IsPublished] [bit] NULL,
    [IsDistributor] [bit] NULL,
    [IsEncrypted] [bit] NULL,
    [IsCdcEnabled] [bit] NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_Database_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_Database] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_Database] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'DatabaseID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库大小MB' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'DatabaseSize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'StateDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库所有者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'OwnerName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库排序规则' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'CollationName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库恢复模式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'RecoveryModelDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'兼容级别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'CompatibilityLevel'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户访问模式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'UserAccessDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'只读选项' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'ReadOnlyDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'页验证' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'PageVerifyOptionDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否发布数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'IsPublished'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否分发数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'IsDistributor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否加密' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'IsEncrypted'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否变更捕获' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'IsCdcEnabled'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库信息信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_Database'
GO
/****** 对象:  Table [dbo].[Info_DatabaseFiles]    脚本日期: 02/08/2017 15:59:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_DatabaseFiles](
    [InstanceID] [int] NOT NULL,
    [DatabaseName] [varchar](32) NOT NULL,
    [FileId] [int] NOT NULL,
    [LogicalName] [nvarchar](128) NULL,
    [TypeDesc] [nvarchar](60) NULL,
    [FileGroupName] [nvarchar](60) NULL,
    [PhysicalName] [nvarchar](260) NULL,
    [Size] [decimal](18, 2) NULL,
    [UnUsedSize] [decimal](18, 2) NULL,
    [MaxSize] [int] NULL,
    [Growth] [int] NULL,
    [IsPercentGrowth] [bit] NULL,
    [IsReadOnly] [bit] NULL,
    [IsSparse] [bit] NULL,
    [IsNameReserved] [bit] NULL,
    [StateDesc] [nvarchar](60) NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_DatabaseFiles_InsertTime]  DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_InsertTime] ON [dbo].[Info_DatabaseFiles] 
(
    [InsertTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InstanceID] ON [dbo].[Info_DatabaseFiles] 
(
    [InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'InstanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'FileId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'逻辑名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'LogicalName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'TypeDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件组' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'FileGroupName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物理名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'PhysicalName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件大小(MB)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'Size'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已用比例' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'UnUsedSize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大大小(MB,-1不限制)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'MaxSize'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'增长值(Growth=0未开启自增长;IsPercentGrowth=0按固定值增长(MB),IsPercentGrowth=1按百分比增长)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'Growth'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否按百分比增长' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'IsPercentGrowth'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否只读文件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'IsReadOnly'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否稀疏文件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'IsSparse'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = 只有在下一次日志备份之后才能重用删除的文件名(name 或 physical_name)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'IsNameReserved'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'StateDesc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles', @level2type=N'COLUMN',@level2name=N'InsertTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Info_DatabaseFiles'
GO
/****** 对象:  Table [dbo].[Info_LoginRole]    脚本日期: 02/08/2017 15:59:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info_LoginRole](
    [InstanceID] [int] NOT NULL,
    [DatabaseName] [varchar](32) NOT NULL,
    [DatabaseRole] [varchar](64) NOT NULL,
    [RoleMember] [varchar](64) NOT NULL,
    [MemberType] [varchar](64) NOT NULL,
    [LoginName] [varchar](64) NULL,
    [InsertTime] [datetime] NULL CONSTRAINT [DF_LoginRole_InsertTime]  DEFAULT (getdate()),
    [UpdateTime] [datetime] NULL,
    [IsDelete] [int] NULL CONSTRAINT [DF_LoginRole_IsDelete]  DEFAULT ((0))
) ON [PRIMARY]
GO
/****** 对象:  View [dbo].[view_TableSpace]    脚本日期: 02/08/2017 16:00:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[view_TableSpace]
as
with a as(
select *
,row_number() over(partition by InstanceID,DBname order by Reserved desc,TableRows desc) RankID
From dbo.Info_TableSpace
where Inserttime>convert(varchar,getdate(),112)
and isdate(right(TableName,8))=0
)
,b as(
select * From dbo.Info_TableSpace
where Inserttime>convert(varchar,getdate()-7,112) and Inserttime<convert(varchar,getdate()-6,112)
)
select a.InstanceID,a.DBname,a.TableName,a.TableRows
,a.Reserved/1024 ReservedMB,a.DataSize/1024 DataSizeMB,a.IndexSize/1024 IndexSizeMB
,a.TableRows-b.TableRows DiffTableRows
,((a.Reserved-a.Unused)-(b.Reserved-b.Unused))/1024 DiffSizeMB
from a inner join b
on a.InstanceID=b.InstanceID
and a.DBname=b.DBName
and a.TableName=b.TableName
where a.Reserved>100*1024
and a.RankID<=10
GO
/****** 对象:  View [dbo].[view_DatabaseFiles]    脚本日期: 02/08/2017 16:00:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[view_DatabaseFiles]
as
with a as(
select row_number() over(partition by a.InstanceID order by a.DatabaseSize desc,a.DatabaseName,b.FileId) RankId
,b.* 
from(
select InstanceID,DatabaseName,DatabaseSize,StateDesc,LogReuseWaitDesc,RecoveryModelDesc 
from dbo.Info_Database 
where InsertTime>convert(varchar,getdate(),112)
) a
inner join(
select InstanceID,DatabaseName,FileId,LogicalName,TypeDesc
,FileGroupName,PhysicalName,Size,UnUsedSize,MaxSize
,case IsPercentGrowth when 1 then cast(Growth as varchar)+'%' else cast(Growth as varchar) end as Growth
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate(),112)
) b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
)
,b as(
select b.* 
from(
select InstanceID,DatabaseName,DatabaseSize,StateDesc,LogReuseWaitDesc,RecoveryModelDesc 
from dbo.Info_Database 
where InsertTime>convert(varchar,getdate()-7,112) and InsertTime<convert(varchar,getdate()-6,112)
) a
inner join(
select InstanceID,DatabaseName,FileId,LogicalName,TypeDesc
,FileGroupName,PhysicalName,Size,UnUsedSize,MaxSize
,case IsPercentGrowth when 1 then cast(Growth as varchar)+'%' else cast(Growth as varchar) end as Growth
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate()-7,112) and InsertTime<convert(varchar,getdate()-6,112)
) b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
)
select a.RankId,a.InstanceID,a.DatabaseName,a.FileId,a.LogicalName,a.TypeDesc
,a.FileGroupName,a.PhysicalName,a.Size,a.UnUsedSize
,(a.Size-a.UnUsedSize)-(b.Size-b.UnUsedSize) as DiffSize
,a.Growth,a.MaxSize
from a
inner join b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
and a.LogicalName=b.LogicalName
GO
/****** 对象:  View [dbo].[view_Database]    脚本日期: 02/08/2017 16:00:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[view_Database]
as
with a as(
select row_number() over(partition by a.InstanceID order by a.DatabaseSize desc,a.DatabaseName) RankId
,a.InstanceID,a.DatabaseName,a.DatabaseSize
,b.DataFileSize,c.LogFileSize,b.UnUsedDataFileSize
,a.StateDesc,a.LogReuseWaitDesc,a.RecoveryModelDesc
from(
select InstanceID,DatabaseName,DatabaseSize,StateDesc,LogReuseWaitDesc,RecoveryModelDesc
from dbo.Info_Database 
where InsertTime>convert(varchar,getdate(),112)
) a
inner join(
select InstanceID,DatabaseName,sum(Size) DataFileSize,sum(UnUsedSize) UnUsedDataFileSize
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate(),112)
and TypeDesc='ROWS'
group by InstanceID,DatabaseName
) b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
inner join(
select InstanceID,DatabaseName,max(Size) LogFileSize
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate(),112)
and TypeDesc='LOG'
group by InstanceID,DatabaseName
)c
on a.InstanceID=c.InstanceID
and a.DatabaseName=c.DatabaseName
)
,b as(
select a.InstanceID,a.DatabaseName,a.DatabaseSize
,b.DataFileSize,c.LogFileSize,b.UnUsedDataFileSize
,a.StateDesc,a.LogReuseWaitDesc,a.RecoveryModelDesc 
from(
select InstanceID,DatabaseName,DatabaseSize,StateDesc,LogReuseWaitDesc,RecoveryModelDesc 
from dbo.Info_Database 
where InsertTime>convert(varchar,getdate()-7,112) and InsertTime<convert(varchar,getdate()-6,112)
) a
inner join(
select InstanceID,DatabaseName,sum(Size) DataFileSize,sum(UnUsedSize) UnUsedDataFileSize
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate()-7,112) and InsertTime<convert(varchar,getdate()-6,112)
and TypeDesc='ROWS'
group by InstanceID,DatabaseName
) b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
inner join(
select InstanceID,DatabaseName,max(Size) LogFileSize
from dbo.Info_DatabaseFiles 
where InsertTime>convert(varchar,getdate()-7,112) and InsertTime<convert(varchar,getdate()-6,112)
and TypeDesc='LOG'
group by InstanceID,DatabaseName
)c
on a.InstanceID=c.InstanceID
and a.DatabaseName=c.DatabaseName
)
select a.RankId,a.InstanceID,a.DatabaseName,a.DatabaseSize
,a.DataFileSize,a.LogFileSize,a.UnUsedDataFileSize
,a.DatabaseSize-b.DatabaseSize DiffDatabaseSize
,(a.DataFileSize-a.UnUsedDataFileSize)-(b.DataFileSize-b.UnUsedDataFileSize) as DiffDataSize
,a.StateDesc,a.LogReuseWaitDesc,a.RecoveryModelDesc
from a
inner join b
on a.InstanceID=b.InstanceID
and a.DatabaseName=b.DatabaseName
GO

View Code

基础表数据查询

--每周日获取系统硬件信息
IF DATEPART(dw,GETDATE())=1
BEGIN
    exec sp_configure 'show advanced options', 1
    reconfigure
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
    exec DBA_Pro_Get_SystemInfo 1002
    exec sp_configure 'xp_cmdshell', 0
    reconfigure 
    exec sp_configure 'show advanced options', 0
    reconfigure
END
exec xp_cmdshell 'wmic LogicalDisk get Description,Caption,Size'
SELECT * FROM sys.configurations c order by name

--监控-获取硬件_实例_数据库信息
select top 100 * from dbo.Info_Hardware
select top 100 * from dbo.Info_Instance
select top 100 * from dbo.Info_Database
select top 100 * from dbo.Info_DatabaseFiles
select top 100 * from dbo.Login_LoginsInfo --delete+insert
select top 100 * from dbo.Login_PasswordSetLog --insert
--监控-获取错误日志_作业执行错误信息
select top 100 * from dbo.Info_ErrorLog --etltime
select top 100 * from dbo.Info_JobHistory --etltime
--监控-获取表大小_登录名权限信息
select top 100 * from dbo.Info_TableSpace
select top 100 * From dbo.Info_LoginRole --delete+insert
--监控-CPU使用率_磁盘空间
select top 100 * From dbo.Info_CpuUseLog --etltime
select top 100 * from dbo.Info_DiskSpace
--监控-阻塞检查
select top 100 * From dbo.Info_Blocked --etltime

--执行错误信息
select top 100 * from dbo.ExecErrorLog order by inserttime desc
select top 100 * from dbo.SSIS_PackageErrorLog order by inserttime desc
--目标配置信息
select * from dbo.Info_Server --local
select * from dbo.SSIS_Configuration 
--视图查询
select * from dbo.view_Database
select * From dbo.view_TableSpace

--清空数据
USE DBA_Monitor
GO
--sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

--网络映射
exec xp_cmdshell 'c:\windows\system32\net.exe use'
exec xp_cmdshell 'Dir M:\247\PLAY\*.bak /od'
exec xp_cmdshell 'c:\windows\system32\net.exe use L: /del'

View Code

SSIS汇集数据全部调用存储过程，尽量限制相关登录名的权限。
【DDL触发器】在需要监控的用户数据库上，使用以下语句创建数据库触发器

USE [DBA_Monitor]
GO
/****** 对象:  DdlTrigger [trgLogDDLEvent]    脚本日期: 02/08/2017 15:42:58 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE name = N'trgLogDDLEvent' AND parent_class=0)
DROP TRIGGER [trgLogDDLEvent] ON DATABASE
GO
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE @data XML
    DECLARE @SqlText nvarchar(max)
    DECLARE @InstanceID INT
    SET @InstanceID=100233
    
    SET @data = EVENTDATA()
    SET @SqlText=@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_STATISTICS' 
       and @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') not like '%EXTENDED_PROPERTY'--排除扩展属性2014/12/03
        INSERT  INTO DBA_Monitor.dbo.Info_DDLChangeLog
                (
                  EventType,
                  ObjectName,
                  ObjectType,
                  [TSQL],
                  [SQLType],
                  DBName,
                  InstanceID
                )
        VALUES  (
                  @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),
                  @SqlText,
                  (Case When @SqlText like '%While%' then 1
                        When @SqlText like '%Cursor%' then 2
                        When @SqlText like '%begin transaction%' then 3 else 0 end),
                  DB_NAME(),
                  @InstanceID                          
                ) ;


GO
DISABLE TRIGGER [trgLogDDLEvent] ON DATABASE
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
GO

View Code

监控数据汇总后，根据重要程度发送邮件(错误日志、作业异常、CPU使用率、监控汇集数据异常在获取到目标服务器后马上发送，可用空间、阻塞信息每1天发送一次)，其他预警邮件
【数据库阻塞】

USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_Blocked]    脚本日期: 02/08/2017 16:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：邮件数据库阻塞
  最新修改：2016/12/06
  调用示例: exec RPT_Blocked 'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_Blocked]
@Recipients    VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON
    declare @Subject varchar(64)
    set @Subject='[ALL][重要] 数据库阻塞预警'

    if exists(select top 1 1 FROM Info_Blocked where InsertTime>=convert(varchar,getdate()-1,112)+' 09:00:00'
            and InsertTime<convert(varchar,getdate(),112)+' 09:00:00')
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>数据库阻塞预警</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>被阻塞会话</th><th>数据库名称</th><th>等待类型</th><th>等待资源</th>
        <th>阻塞时间</th><th>阻塞时长(秒)</th><th>被阻塞会话执行语句</th><th>阻塞会话</th><th>阻塞会话持有资源</th><th>阻塞会话执行语句</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID,BlockedTime,BlockedSessionId),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = BlockedSessionId,'',
                    td = DatabaseName,'',
                    td = WaitType,'',
                    td = isnull(WaitResource,''),'',
                    td = convert(varchar(19),BlockedTime,121),'',
                    td = WaitDuration_ms/1000,'',
                    td = BlockedBatch,'',
                    td = BlockingSessionId,'',
                    td = isnull(BlockingHoldResource,''),'',
                    td = BlockingBatch,''
                 FROM Info_Blocked a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 where InsertTime>=convert(varchar,getdate()-1,112)+' 09:00:00'
                 and InsertTime<convert(varchar,getdate(),112)+' 09:00:00'
                 ORDER BY a.InstanceID,BlockedTime,BlockedSessionId
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：报表仅列出阻塞基本信息，更多详细信息可参考[阻塞信息表]'
        +'<br /><br />邮件发送条件：
        <br />1、每天发送前一天09:00~当天09:00的阻塞情况';
        --print @tableHTML;
    end

    IF (@tableHTML is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';

END

View Code

【监控作业异常】

USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_ExecError]    脚本日期: 02/08/2017 16:49:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：邮件监控作业异常
  最新修改：2016/11/29
  调用示例: exec RPT_ExecError 'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_ExecError]
@Recipients    VARCHAR(200)
AS
BEGIN
    declare @StartTime datetime
    declare @Subject varchar(64)
    --数据获取后，只需取ETLTime最近几分钟的数据，对应的是自上次后的所有数据
    set    @StartTime=dateadd(mi,-10,getdate())
    set @Subject='[ALL][重要] 监控作业异常'

    select * into #ExecError from ExecErrorLog where ETLTime>@StartTime

    if exists(select top 1 1 FROM #ExecError)
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>监控作业异常</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>存储过程名称</th><th>错误号</th><th>错误消息</th><th>严重级别</th><th>状态号</th><th>行号</th><th>出错时间</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID,a.InsertTime desc),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = isnull(a.Error_Procname,''),'',
                    td = a.Error_Numbers,'',
                    td = a.Error_Messages,'',
                    td = a.Error_Serverity,'',
                    td = a.Error_States,'',
                    td = a.Error_Lines,'',
                    td = convert(varchar(19),a.InsertTime,121),''
                 FROM #ExecError a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 ORDER BY a.InstanceID,a.InsertTime desc
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：此报表表明监控-**相关作业遇到CATCH异常(作业不会报错，但存储过程有异常)，请根据主机名、存储过程名称和错误消息单独处理<br />';
        --print @tableHTML;
    end

    IF (@tableHTML is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';

    if object_id('tempdb..#ExecError')is not null
        drop table #ExecError
END

View Code

【SSIS汇集数据异常】

USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_PackageError]    脚本日期: 02/08/2017 16:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：邮件SSIS汇集数据异常
  最新修改：2016/12/09
  调用示例: exec RPT_PackageError 'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_PackageError]
@Recipients    VARCHAR(200)
AS
BEGIN
    declare @StartTime datetime
    declare @Subject varchar(64)
    --包执行后，只需取InsertTime最近几分钟的数据，对应的是自上次后的所有数据
    set    @StartTime=dateadd(mi,-10,getdate())
    set @Subject='[ALL][重要] SSIS汇集数据异常'

    select * into #PackageError from SSIS_PackageErrorLog where InsertTime>@StartTime

    if exists(select top 1 1 FROM #PackageError)
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>SSIS汇集数据异常</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>数据源</th><th>包名称</th><th>任务名称</th><th>错误消息</th><th>出错时间</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.ID),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = a.PackageName,'',
                    td = a.TaskName,'',
                    td = a.ErrorDescription,'',
                    td = convert(varchar(19),a.InsertTime,121),''
                 FROM #PackageError a
                 inner join SSIS_Configuration c
                 on a.SourceServerName=c.SourceServerName
                 inner join Info_Server b
                 on c.ServerID=b.ServerID
                 ORDER BY a.ID
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：此报表表明汇集监控数据的SSIS包执行出错，请根据数据源和错误消息单独处理<br />';
        --print @tableHTML;
    end

    IF (@tableHTML is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';

    if object_id('tempdb..#PackageError')is not null
        drop table #PackageError
END

View Code

posted @ 2017-02-18 11:11 Uest 阅读(273) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？

公告

您的浏览器不支持html5的canvas

关注技术：MySQL、Go
联系方式：UestMail@163.com

随笔、评论滚动鼠标查看更多

昵称： Uest
园龄： 11年3个月
粉丝： 47
关注： 23

+加关注

监控-补充

公告

搜索

随笔分类 (155)

随笔档案 (186)

推荐链接

最新评论

大神，为什么我拖入XML没有生成CHM文件啊？

	【作者】：醒嘞
	【出处】： http://www.cnblogs.com/Uest/
	【声明】：本文内容仅代表个人观点。如需转载请保留此段声明，且在文章页面明显位置给出原文链接！