Querying the size of a table in a SQL database

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

EXEC sp_spaceused ' table name '

2. Exact table space size, but may take some time to count

EXEC sp_spaceused ' table name ', True

3. Database Size Query

EXEC sp_spaceused

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

exec sp_msforeachtable "exec sp_spaceused '?"

5. All User table Space table is small, big database is used with caution

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 * from #t ORDER by rows DESC

drop table #t

Sp_msforeachtable How to use

1) Description

The system stored procedures sp_msforeachtable and SP_MSFOREACHDB are two unlisted stored procedures provided by Microsoft, starting with MS SQL 6.5.

stored in the master database of SQL Server.

2) Parameter Description:

@command1 nvarchar (2000),--The first SQL command to run

@replacechar nchar (1) = N '? ',--the specified placeholder symbol

@command2 nvarchar = null,--the second SQL command to run

@command3 nvarchar = null,--third run SQL instruction

@whereand nvarchar = null,--optional condition to select table

@precommand nvarchar = null, the action before the instruction is executed (similar to the action before the trigger of the control)

@postcommand nvarchar = null-the action after the instruction is executed (similar to the trigger action of the control)

3) Example

--Details of each table in the statistical database

exec sp_msforeachtable @command1 = "sp_spaceused '?"

--Get the number of records and the capacity of each table:

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

@command2 = "sp_spaceused '? '",

@command3 = "SELECT count (*) from?"

--Get all the storage space for the database:

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

@command2 = "sp_spaceused"

--Check all the databases

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

@command2 = "DBCC CHECKDB (?) "

--Update STATISTICS for all tables in the pubs database that already start with T:

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 in the current database

Sp_msforeachtable @command1 = ' Delete from? '

Sp_msforeachtable @command1 = "TRUNCATE TABLE?"

4) Use of parameter @whereand

@whereand parameters play an important role in the instruction condition in the stored procedure, and the specific wording is as follows:

@whereend, you can write @whereand = ' and O.name in (' Table1 ', ' Table2 ',.......) '

For example: I want to update the value of note column NULL 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 use of stored procedures has created these two powerful stored procedures

Here "?" is equivalent to the role of a wildcard in DOS commands and when we search for files under Windows.

Querying the size of a table in a SQL database