Scripts for querying all the records of all tables in the current database

xiaoxiao2021-03-06  81

- ================================================================================================================================================================== ========================== - 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

转载请注明原文地址:https://www.9cbs.com/read-123274.html

New Post(0)