Queries the space occupied by tables in the SQL database.

1. SQL statistics, which may be inaccurate after a large number of transaction operations

Exec sp_spaceused 'table name'

2. Accurate tablespace size, but it may take some time for Statistics

Exec sp_spaceused 'table name', true

3. database size Query

Exec sp_spaceused

4. All user tablespace tables are small, SQL statistics, and may be inaccurate after a large number of transaction operations

Exec sp_MSforeachtable "exec sp_spaceused '? '"

5. All user tablespace tables are small and large databases should be used with caution.

Exec sp_MSforeachtable "exec sp_spaceused '? ', True"

Create table # t (

Name varchar (200 ),

Rows int,

Reserved varchar (25 ),

Data varchar (25 ),

Index_size varchar (25 ),

Unused varchar (25 ))

Exec sp_MSforeachtable "insert into # t exec sp_spaceused '? '"

Select TOP 100 * from # t order by rows DESC

Drop table # t

Sp_MSforeachtable usage

1) Description

System stored procedures sp_MSforeachtable and sp_MSforeachdb are two undisclosed stored procedures provided by Microsoft, starting from ms SQL 6.5.

Stored in the MASTER database of SQL Server.

2) parameter description:

@ Command1 nvarchar (2000), -- the first running SQL command

@ Replacechar nchar (1) = n '? ', -- The specified placeholder.

@ Command2 nvarchar (2000) = null, -- the second running SQL command

@ Command3 nvarchar (2000) = null, -- the third running SQL command

@ Whereand nvarchar (2000) = null, -- an optional condition to select a table

@ Precommand nvarchar (2000) = null, -- the operation before the command is executed (similar to the operation before the control is triggered)

@ Postcommand nvarchar (2000) = null -- the operation after the command is executed (similar to the operation after the control is triggered)

3) Example

-- Details of each table in the Statistics Database

Exec sp_MSforeachtable @ command1 = "sp_spaceused '? '"

-- Obtain the number and capacity of records for each table:

EXEC sp_MSforeachtable @ command1 = "print '? '",

@ Command2 = "sp_spaceused '? '",

@ Command3 = "SELECT count (*) FROM? "

-- Obtain the storage space of all databases:

EXEC sp_MSforeachdb @ command1 = "print '? '",

@ Command2 = "sp_spaceused"

-- Check all databases

EXEC sp_MSforeachdb @ command1 = "print '? '",

@ Command2 = "dbcc checkdb (?) "

-- Update statistics of all tables starting with "t" in the PUBS database:

EXEC sp_MSforeachtable @ whereand = "and name like't % '",

@ Replacechar = '*',

@ Precommand = "print 'updating Statistics... 'print ''",

@ Command1 = "print '*' update statistics *",

@ Postcommand = "print ''print 'complete Update Statistics! '"

-- Delete data from all tables of the current database

Sp_MSforeachtable @ command1 = 'delete from? '

Sp_MSforeachtable @ command1 = "truncate table? "

4) parameter @ whereand usage

@ Whereand the parameter plays a role as a command condition restriction in the stored procedure. The specific statement is as follows:

@ Whereend, you can write @ whereand = 'AND o. name in (''table1', ''table2 '',.......)'

For example, I want to update the NULL value of the NOTE column in Table1/Table2/Table3.

Sp_MSforeachtable @ command1 = 'Update? Set NOTE = ''' Where NOTE is null', @ whereand = 'AND o. name in (''table1', ''table2', ''table3 '')'

5 )"? "The special usage of stored procedures has created these two powerful stored procedures

Here "? "Is equivalent to the wildcard in the doscommand and when we search for files in WINDOWS.