SqlServer 监控数据库中各个表的数据条数

1.创建监控表：

CREATE TABLE [dbo].[MonitorDataVolume]([Id] [bigint] IDENTITY(1,1) NOT NULL,[ServerIP] [varchar](30) NULL,[ServerName] [nvarchar](50) NULL,[ClientIP] [varchar](30) NULL,[ClientName] [nvarchar](50) NULL,[DatabaseName] [nvarchar](50) NULL,[TableName] [nvarchar](50) NULL,[TableTotalCount] [bigint] NULL,[TableDescription] [nvarchar](100) NULL,[CreatedTime] [datetime2](7) NOT NULL,[LastUpdatedTime] [datetime2](7) NOT NULL,[IsDeleted] [bit] NOT NULL,[CreatedUserId] [varchar](50) NOT NULL,[LastUpdatedUserId] [varchar](50) NOT NULL,[Remark] [nvarchar](100) NULL, CONSTRAINT [PK_MonitorDataVolume] PRIMARY KEY CLUSTERED ([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_CreatedTime]  DEFAULT (getdate()) FOR [CreatedTime]GOALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_LastUpdatedTime]  DEFAULT (getdate()) FOR [LastUpdatedTime]GOALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]GOALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_CreatedUserId]  DEFAULT ('System') FOR [CreatedUserId]GOALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_LastUpdatedUserId]  DEFAULT ('System') FOR [LastUpdatedUserId]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'Id'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'ServerIP'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'ServerName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户端IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'ClientIP'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户端机器名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'ClientName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'DatabaseName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据表名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'TableName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表中的数据量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'TableTotalCount'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'TableDescription'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间（这条业务数据产生的时间）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'CreatedTime'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'LastUpdatedTime'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'逻辑删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'IsDeleted'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人(标识)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'CreatedUserId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人(标识)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'LastUpdatedUserId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume', @level2type=N'COLUMN',@level2name=N'Remark'GOEXEC sys.sp_addextendedproperty @name=N'Description', @value=N'监控数据库表数据条数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MonitorDataVolume'GO

2.将相应的表数据 插入到 监控表中：

DECLARE @ClientIP varchar(30),@ServerIP varchar(30),@ClientName nvarchar(100),@ServerName nvarchar(100),@DbName nvarchar(50)select @ServerIP = local_net_address, @ClientIP = client_net_address,@DbName = db_name(),@ClientName = HOST_NAME(),@ServerName = @@servername from sys.dm_exec_connections where Session_id=@@spidINSERT INTO MonitorDataVolume ( ServerIP,ServerName ,ClientIP,ClientName,DatabaseName,TableName,TableTotalCount,TableDescription,Remark) SELECT @ServerIP, @ServerName,@ClientIP,@ClientName,@DbName,a.name,convert(bigint,b.rows) TotalCount,convert(nvarchar(100),D.Description),'数据来源：SqlServer定时计划任务'FROM sysobjects a  INNER JOINsysindexes b ON a.id=b.id   join (select a.name AS name,isnull(g.[value], '未定义') AS Descriptionfrom sys.tables a left join (SELECT * FROM sys.extended_properties WHERE NAME='Description' ) gon (a.object_id = g.major_id AND g.minor_id = 0) ) DON D.name = A.nameWHERE b.indid IN(0,1) AND a.Type='U' ORDER BY b.rows  desc

建议 ：

将第二步中的Sql 做成SqlServer的定时计划任务 进行运行