|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
- Scenarios where you need to query the number of records in all tables and sort by the number of records.
- 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
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.