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,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',@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'TableTotalCount'
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'LastUpdatedTime'
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'LastUpdatedUserId'
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的定时计划任务 进行运行

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!