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

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,[Table@R_558_10586@lCount] [bigint] NULL,[TableDescription] [nvarchar](100) NULL,[CreatedTime] [datetiR_960_11845@e2](7) NOT NULL,[@R_197_2921@Time] [datetiR_960_11845@e2](7) NOT NULL,[Isdeleted] [bit] NOT NULL,[CreatedUserId] [varchar](50) NOT NULL,[@R_197_2921@UserId] [varchar](50) NOT 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_@R_197_2921@Time]  DEFAULT (getdate()) FOR [@R_197_2921@Time]
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_@R_197_2921@UserId]  DEFAULT ('System') FOR [@R_197_2921@UserId]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'标识列',@level0type=N'scheR_960_11845@A',@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',@level2name=N'ServerIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'服务器名称',@level2name=N'ServerName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'客户端IP',@level2name=N'ClientIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'客户端机器名称',@level2name=N'ClientName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'数据库名称',@level2name=N'Databasename'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'数据表名称',@level2name=N'Tablename'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'表中的数据量',@level2name=N'Table@R_558_10586@lCount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'表说明',@level2name=N'TableDescription'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'创建时间（这条业务数据产生的时间）',@level2name=N'CreatedTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'最后修改时间',@level2name=N'@R_197_2921@Time'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'逻辑删除',@level2name=N'Isdeleted'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'创建人(标识)',@level2name=N'CreatedUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'最后修改人(标识)',@level2name=N'@R_197_2921@UserId'
GO

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,Table@R_558_10586@lCount,TableDescription,REMARK
) 

SELECT @ServerIP,@ServerName,@ClientIP,@ClientName,@dbname,a.name,convert(bigint,b.rows) @R_558_10586@lCount,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的定时计划任务 进行运行@H_197_42@