The number of new rows and storage space added per day for a statistical table.

The number of new rows and storage space added per day for a statistical table.

Use the Code provided in this article to make a statistical table for the new number of rows per day and the new storage space function

The implementation steps are as follows:

1. Create a table

Create a table to store the daily tablespace usage

CREATE TABLE [dbo].[t_rpt_table_spaceinfo]([table_name] [sysname] NOT NULL,[record_date] [date] NOT NULL,[record_time] [time](7) NOT NULL,[rows_count] [bigint] NULL,[reserved] [bigint] NULL,[data_size] [bigint] NULL,[index_size] [bigint] NULL,[unused] [bigint] NULL,CONSTRAINT [PK_t_rpt_table_spaceinfo] PRIMARY KEY CLUSTERED ([table_name] ASC,[record_date] ASC,[record_time] ASC))

2. Create a job

Create a new job. The job is scheduled to run once every morning. the space occupied by the table is recorded every day and stored in the table created in the previous step.

The T-SQL code executed in the job is:

Set nocount on/* CREATE a temporary TABLE to store the space and data rows of the User TABLE */create table # tablespaceinfo (nameinfo VARCHAR (500), rowsinfo BIGINT, reserved VARCHAR (20 ), datainfo VARCHAR (20), index_size VARCHAR (20), unused VARCHAR (20) DECLARE @ tablename VARCHAR (255);/* Use cursor, cyclically obtain table space usage */DECLARE Info_cursor CURSORFORSELECT '[' + [name] + ']' FROM sys. tablesWHERE type = 'U '; OPEN tables fetch next from Info_cursor INTO @ tablename WHILE @ FETCH_STATUS = 0 begin insert into # using sp_spaceused @ tablename fetch next from Info_cursor INTO @ tablename end insert into partition (record_date, record_time, [table_name], [rows_count], reserved, [data_size], index_size, unused) SELECT convert (date, getdate (), convert (varchar (8), getdate (), 114), nameinfo, rowsinfo, CAST (REPLACE (reserved, 'kb', '') as bigint), CAST (REPLACE (datainfo, 'kb ','') as bigint), CAST (REPLACE (index_size, 'kb', '') as bigint), CAST (REPLACE (unused, 'kb','') as bigint) FROM # tablespaceinfoCLOSE Info_cursor DEALLOCATE Info_cursor drop table [# tablespaceinfo]

3. query results

Compare consecutive data records to obtain incremental data changes.

The sample code is as follows:

With table_spaceinfo as (select record_date, record_time, table_name, rows_count, reserved, data_size, index_size, unused, ROW_NUMBER () over (PARTITION by table_name order by record_date, record_time asc) as list_nofrom t_rpt_table_spaceinfo) select _. table_name as table name, convert (varchar (20), _. record_date) + ''+ convert (varchar (8), _. record_time) + '~~ '+ Convert (varchar (20), _ B. record_date) + ''+ convert (varchar (8), _ B. record_time) as [time range], _ B. rows_count-_. rows_count as [new rows], _ B. data_size-_. data_size as [new data space (KB)] from table_spaceinfo _ ajoin table_spaceinfo _ B on _. table_name = _ B. table_name and _. list_no = _ B. list_no-1order by [time range]

Use EXCEL to count the number of rows. One workbook has multiple worksheets and some rows have no data. I need to count the number of rows in each worksheet.

= COUNT (A2: A26) refers to a row

The Code statistics Tool must support differential statistics, such as changing the number of lines in the code, deleting the number of lines, and adding new lines.

TortoiseSVN is easy to use. The detailed usage has been sent to your mailbox.
Yes !!!