- ================================================================================================================================================================== ========================== - Description: This script is used to query the number of records recorded all the tables in the current database - and save the results In the tableinfo table, it will not be deleted to prepare the user to do processing and analysis - but, finally, please ask the user to delete this table. - ================================================================================================================================================================== ==========================
IF exissrs (select * from dbo.sysObjects where id = Object_id (n '[dbo]') And ObjectProperty (ID, n'uSERTABLE ') = 1) DROP TABLE [DBO]. [TABLESPACE] Go
Create Table TableSpace (Tablename Varchar (20), RowsCount Char (11), Reserved Varchar (18), Data Varchar (18), INDEX_SIZE VARCHAR (18), Unuse Varchar (18)) Go
Declare @SQL VARCHAR (500) Declare @tablename Varchar (20)
Declare Cursor1 Cursorfor Select Name from sysobjects where xtype = 'u'
Open cursor1fetch next from cursor1 inTo @tablename
while @@ fetch_status = 0begin set @sql = 'insert into TableSpace' set @sql = @sql 'exec sp_spaceused' '' @TableName '' '' exec (@sql) fetch next from Cursor1 into @TableNameendclose Cursor1deallocate Cursor1go
- Display result Select * from tablespace - Order by Tablename - Order by Tablename ASC - Table Name for Statistics Table - ORDER BY ROWSCOUNT DESC - Number of travel, used to view the number of tables --ORDER BY RESERVED DESC, DATA DESC - Press User Space - Orsold by INDEX_SIZE DESC, RESERVED DESC - View the use of the GO - View the use of the library by index space, can be performed at any time. --exec sp_spaceused - Go