Script to query the number of records for all tables in the current database

Scripts | data | database
--==========================================================================
--Note: This script is used to query the number of record bars for all tables in the current database
--and the results are saved in the Tableinfo table and will not be deleted for user to do processing and analysis
--However, the user is finally asked to delete this table.
--==========================================================================

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Tablespace] and OBJECTPROPERTY (ID, N ' isusertable ') = 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),
Unused varchar (18)
)
Go

DECLARE @sql varchar (500)
DECLARE @TableName varchar (20)

DECLARE Cursor1 Cursor
For
Select name from sysobjects where xtype= ' U '

Open Cursor1
FETCH NEXT from Cursor1 into @TableName

While @ @fetch_status = 0
Begin
Set @sql = ' INSERT INTO tablespace '
Set @sql = @sql + ' exec sp_spaceused ' + @TableName + ' "'
EXEC (@sql)
FETCH NEXT from Cursor1 into @TableName
End
Close Cursor1
Deallocate Cursor1
Go


--Show results
SELECT * FROM Tablespace
--order by TableName
--order by TableName ASC--per table name, for statistical tables
--order by Rowscount DESC--The number of rows used to view the number of table rows
--order by Reserved DESC, Data DESC--occupied space
--order by Index_size DESC, Reserved DESC--View by index space
Go

--View the usage of the library and can be executed at any time.
--exec sp_spaceused
--go