SQL Server queries the number of data entries in all tables in the database.

What if we want to get the number of all tables in the database? Let's look at several of the most common methods:

-- Method 1

The code is as follows:	Copy code
Select B. name as tablename, C. row_count as datacount From sys. indexes, Sys. objects B, Sys. dm_db_partition_stats c Where a. [object_id] = B. [object_id] AND B. [object_id] = c. [object_id] AND a. index_id = c. index_id AND a. index_id <2 AND B. is_ms_shipped = 0

-- Method 2

The code is as follows:	Copy code
Select B. name as tablename, A. rowcnt as datacount From sysindexes, Sysobjects B Where a. id = B. id And a. indid <2 And objectproperty (B. id, 'ismsshipped ') = 0

-- Method 3

The code is as follows:

Copy code

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 mCursor cursor For Select name from sysobjects where xtype = 'u' Open mCursor Fetch NEXT from mCursor into @ TableName While @ fetch_status = 0 Begin Set @ SQL = 'Insert into tablespace' Set @ SQL = @ SQL + 'exec sp_spaceused ''' + @ TableName + '''' Exec (@ SQL) Fetch NEXT from mCursor into @ TableName End Close mCursor Deallocate mCursor Go -- Display results Select TableName, RowsCount from TableSpace

       
-- The last two methods are recommended. For SQL SERVER 2005, the three methods are good. If it is another board, you can test them one by one.

Method 4

-- ===================================================== ==========================================
-- Note: This script is used to query the number of records in all tables in the current database.
-- And save the result to the tableinfo table. It will not be deleted for further processing and analysis.
-- However, please delete the table.
-- ===================================================== ==========================================

The code is as follows:

Copy code

If exists (select * from dbo. sysobjects where id = object_id (n [dbo]. [tablespace]) and objectproperty (id, nisusertable) = 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 -- Display results Select * from tablespace -- Order by tablename -- Order by tablename asc -- by table name, used to count tables -- Order by rowscount desc -- displays the number of rows in the table. -- Order by reserved desc, data desc -- by occupied space -- Order by index_size desc, reserved desc -- View by index space Go -- View the usage of the database, which can be executed at any time. -- Exec sp_spaceused -- Go