SQL Server cursor Usage: View all table size information for a database

I. BACKGROUND

In the performance tuning or need to understand a database table information, the most intuitive way is to list the data of all the table information, including: The number of records of the table, data capture space, index footprint, unused space, etc. (as shown in Figure1), With this information you can simply determine that the pressure on the database from the data may be caused by a certain table. Because the larger the table data, the greater the impact on database performance.

To implement information for all tables in a database, you can obtain the corresponding data in the form of a cursor, and the following figure Figure1 returns information about all the tables in a database:

(Figure1: All table information for a database)

You may not be content with Figure1 information, you want to get information about all the tables in all the databases in the entire database instance (as shown in Figure2), and if you want to know what's inside, consider the following: SQL Server view all table size information for all databases (sizes of all Tables in all Database)

(Figure2: All table information for all databases)

Second, realize

First, define a temporary table variable @tablespaceinfo the information used to hold the table, use the cursor to read the table name in Sys.tables, and then insert the relevant data from the sp_spaceused to the TEMP table variable @tablespaceinfo. The following is an implementation of the SQL script, as shown in Figure1:

--SCRIPT1:
--View information about all tables in a database
DECLARE @tablespaceinfo table (
    [name] SYSNAME,
    [rows] BIGINT,
    [ Reserved] VARCHAR, [
    data] VARCHAR (m),
    [index_size] VARCHAR (MB),
    [unused] VARCHAR (
m)
     
DECLARE @tablename VARCHAR (255);
     
DECLARE info_cursor cursor for
    SELECT ' [' +[name]+ '] ' from sys.tables WHERE type= ' U ';
     
OPEN info_cursor
FETCH NEXT from Info_cursor to @tablename while
     
@ @FETCH_STATUS = 0
BEGIN
    INSERT Into @tablespaceinfo EXEC sp_spaceused @tablename
         
    FETCH NEXT from Info_cursor to @tablename end close
     
Info_cursor
deallocate info_cursor
     
SELECT * from @tablespaceinfo
    ORDER by Cast (Replace (reserved, ' KB ', ") as INT) DESC