SQL Server obtains the names and data entries of all tables in the database.

When we mention the number of data entries in a single table, we will think of select count (*) from tablename

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 if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tablespace] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [tablespace] gocreate table tablespace (tablename varchar (20), rowscount char (11), reserved varchar (18), data varchar (18), index_size varchar (18 ), unused varchar (18) godeclare @ SQL varchar (500) Declare @ tablename varchar (20) Declare mcursor curs Orforselect name from sysobjects where xtype = 'U' open mcursorfetch next from mcursor into @ tablenamewhile @ fetch_status = 0 begin set @ SQL = 'insert into tablespace' set @ SQL = @ SQL + 'exec sp_spaceused ''' + @ tablename + ''' exec (@ SQL) fetch next from mcursor into @ tablename endclose mcursordeallocate mcursorgo -- display the result select tablename, rowscount from tablespace -- method 2 select B. name as tablename, C. row_count as datacountfrom sys. indexes A, sys. objects B, sys. dm_db_partition_stats cwhere. [object_id] = B. [object_id] and B. [object_id] = C. [object_id] and. index_id = C. index_id and. index_id <2 and B. is_ms_shipped = 0 -- Method 3 select B. name as tablename,. rowcnt as datacountfrom sysindexes A, sysobjects bwhere. id = B. ID and. indid <2 and objectproperty (B. ID, 'ismsshipped ') = 0 -- recommended The last two methods are used. For SQL Server 2005, the three methods are good. If it is another board, you can test them one by one.