MSSQL · query and sort the records of all tables in a database

Time:2021-7-28
Reading time |0.27 minutes word count |444 characters
primary coverage |1. Introduction & background
“MSSQL · query and sort the records of all tables in a database”
Author | SCscHero Writing time | 2021/7/13 PM10:0
Article type |Series Degree of completion |Completed
motto Every great cause has a trivial beginning.

1、 Introduction & background    Completion: 100%


a) Dealing with problems

How to query and sort the records of all data tables in a database in MSSQL?

b) Application scenario

  1. Scenarios where you need to query the number of records in all tables and sort by the number of records.
  2. For example, when generating scripts for data migration, in order to avoid too large script files, the data tables with large amount of data can be processed separately.

c) Effect display

MSSQL · query and sort the records of all tables in a database

2、 Solution    Completion: 100%


Use MSSQL related system tables, such as sys.tables and sys.extended_ Perform an associated query on properties and sysindexes.

SELECT  ROW_NUMBER() OVER (ORDER BY All_Table.NAME) AS NO, 
			All_ Table. Name as' table name (EN) ',
			Convert (nvarchar (100), isnull (tabledesc. [value], '-') as' table name (CN) ',
			Tabindex.rows as' number of records'
			FROM SYS.TABLES All_Table 
LEFT JOIN SYS.EXTENDED_ PROPERTIES TableDesc ON (All_ Table.OBJECT_ ID = TableDesc.MAJOR_ ID AND TableDesc.MINOR_ Id = 0) -- take the name of the table and two conditions
INNER JOIN SYSINDEXES TabIndex ON All_Table.OBJECT_ID=TabIndex.ID AND TabIndex.INDID < 2 

WHERE 1=1
 ORDER BY TabIndex.ROWS DESC

3、 Statements and references    Completion: 100%


Original blog post, please do not reprint without permission.

If you are helpful, you are welcome to like, collect and pay attention. If you have any questions, please comment! If you need to contact the blogger, you can write scschero directly.