How SQL Server views all table size information for all databases

I. BACKGROUND

Previously wrote an article about: SQL Server cursor Usage: View a database of all table size information (sizes of all tables in a DB), which lists information about all tables of a data, including the number of records in a table, the amount of space that a data record occupies, Indexing takes up space, unused space, and so on (as Figure1 shows), now I'm going to tell you how to get information about all the tables in all the databases in the entire database instance, as shown in Figure2.

(Figure1: All table information for a database)

(Figure2: All table information for all databases)

Ii. Methods of implementation

The following describes some of the problems encountered during the implementation of the Figure2, and if you are not interested in these issues, you can look at the last implemented SQL script directly. Here are 4 ways to implement this:

1. Cursor + system stored procedure sp_msforeachdb, implement script as SCRIPT3;

2. Package Sp_msforeachtable + sys.databases, implement script for Script4 and SCRIPT5;

3. system stored procedure Sp_msforeachdb + sp_msforeachtable, the realization script is Script6;

4. Extend Sp_msforeachdb + sp_msforeachtable, implement script as Script7;

(a) We are using SQL Server cursors: to review the SQL scripts for all table size information for a database (sizes of all tables in a), and to implement them in conjunction with SP_MSFOREACHDB system stored procedures:

1 Now that you have a script to get all the table information for a database, you can use the SP_MSFOREACHDB system stored procedure in the outer layer, and the following SCRIPT1 script can get information about all the tables in all the databases, as shown in Figure3:

--SCRIPT1:
--View all database all table information
EXEC sp_msforeachdb ' use [?];
    
DECLARE @tablespaceinfo TABLE (
    nameinfo VARCHAR),
    rowsinfo INT, reserved VARCHAR
    (m),
    Datainfo VARCHAR (index_size),
    VARCHAR (
    unused VARCHAR)
    
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
    the ORDER by Cast (Replace (Reserved, ' KB ', ', ' as INT) DESC '