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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_CreatedTime]  DEFAULT (getdate()) FOR [CreatedTime]
GO

ALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_LastUpdatedTime]  DEFAULT (getdate()) FOR [LastUpdatedTime]
GO

ALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_CreatedUserId]  DEFAULT ('System') FOR [CreatedUserId]
GO

ALTER TABLE [dbo].[MonitorDataVolume] ADD  CONSTRAINT [DF_MonitorDataVolume_LastUpdatedUserId]  DEFAULT ('System') FOR [LastUpdatedUserId]
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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'
GO

EXEC 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=@@spid

INSERT 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 JOIN	sysindexes b ON a.id=b.id   
join 
(
		select a.name AS name,isnull(g.[value], '未定义') AS Description
		from sys.tables a left join (SELECT * FROM sys.extended_properties WHERE NAME='Description' ) g	on (a.object_id = g.major_id AND g.minor_id = 0) 
) D				
						ON D.name = A.name
WHERE b.indid IN(0,1) AND a.Type='U' 
ORDER BY b.rows  desc

建议 ：

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