SQL queries the list of all user tables, such as the number of records and the occupied table size.
Exec Sp_msforeachtable" Execute Sp_spaceused ' ? ' "
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
Since mssql6.5, Microsoft has provided two very useful system stored procedures: sp_msforeachtable and sp_msforeachdb, which are used to traverse each table of a database and traverse each database under DBMS management.
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
Execute the following statement in the master database to view the detailedCode
Use Master
Exec Sp_helptext sp_msforeachtable
Exec Sp_helptext sp_msforeachdb
The sp_msforeachtable system stored procedure has seven parameters, which are explained as follows:
@ Command1 Nvarchar ( 2000 ), -- The first T-SQL command to run
@ Replacechar Nchar ( 1 ) = N ' ? ' , -- The specified placeholder.
@ Command2 Nvarchar ( 2000 ) = Null , -- Second run T-SQL command
@ Command3 Nvarchar ( 2000 ) = Null , -- Article 3 run T-SQL instructions
@ Whereand Nvarchar ( 2000 ) = Null , -- (Optional) Select a table.
@ Precommand Nvarchar ( 2000 ) = Null , -- Commands executed before the table
@ Postcommand Nvarchar ( 2000 ) = Null -- Command executed after the table
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
-- Besides @ whereand, sp_msforeachdb is the same as sp_msforeachtable.
-- Let's take a look at the usage of sp_msforeachtable (the usage of sp_msforeachdb is similar ):
-- Details of each table in the statistics database:
Exec Sp_msforeachtable @ Command1 = "Sp_spaceused ' ? ' "
-- Queries the total number of records of all tables in the database.
Create Table # Temp (Tablename Varchar ( 255 ), Rowcnt Int )
Exec Sp_msforeachtable ' Insert into # temp select '' ? '' , Count (*) from? '
Select Tablename, rowcnt From # Temp Order By Tablename
Drop Table # Temp
-- Check the integrity of data, indexes, text, ntext, and image pages of each table or index view in the database.
-- The following statement must be executed in single-user mode (sp_dboption 'db _ name', 'single user', 'true'). If it is set to true, it becomes a multi-user.
Exec Sp_msforeachtable" DBCC Checktable ( ' ? ' , Repair_rebuild )"
SQL queries the list of all user tables, such as the number of records and the occupied table size.
Exec Sp_msforeachtable" Execute Sp_spaceused ' ? ' "
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
Since mssql6.5, Microsoft has provided two very useful system stored procedures: sp_msforeachtable and sp_msforeachdb, which are used to traverse each table of a database and traverse each database under DBMS management.
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
Run the following statement in the master database to view the detailed code of two proc instances.
Use Master
Exec Sp_helptext sp_msforeachtable
Exec Sp_helptext sp_msforeachdb
The sp_msforeachtable system stored procedure has seven parameters, which are explained as follows:
@ Command1 Nvarchar ( 2000 ), -- The first T-SQL command to run
@ Replacechar Nchar ( 1 ) = N ' ? ' , -- The specified placeholder.
@ Command2 Nvarchar ( 2000 ) = Null , -- Second run T-SQL command
@ Command3 Nvarchar ( 2000 ) = Null , -- Article 3 run T-SQL instructions
@ Whereand Nvarchar ( 2000 ) = Null , -- (Optional) Select a table.
@ Precommand Nvarchar ( 2000 ) = Null , -- Commands executed before the table
@ Postcommand Nvarchar ( 2000 ) = Null -- Command executed after the table
Usage of sp_msforeachtable and sp_msforeachdb in MSSQL
-- Besides @ whereand, sp_msforeachdb is the same as sp_msforeachtable.
-- Let's take a look at the usage of sp_msforeachtable (the usage of sp_msforeachdb is similar ):
-- Details of each table in the statistics database:
Exec Sp_msforeachtable @ Command1 = "Sp_spaceused ' ? ' "
-- Queries the total number of records of all tables in the database.
Create Table # Temp (Tablename Varchar ( 255 ), Rowcnt Int )
Exec Sp_msforeachtable ' Insert into # temp select '' ? '' , Count (*) from? '
Select Tablename, rowcnt From # Temp Order By Tablename
Drop Table # Temp
-- Check the integrity of data, indexes, text, ntext, and image pages of each table or index view in the database.
-- The following statement must be executed in single-user mode (sp_dboption 'db _ name', 'single user', 'true'). If it is set to true, it becomes a multi-user.
Exec Sp_msforeachtable" DBCC Checktable ( ' ? ' , Repair_rebuild )"
