MSSQL - Very fast method to get statistic (rows and bytes) for all database tables

Typical way to count all rows in the table ist to send such query:

SELECT COUNT(*) FROM MyTable;

This method has some disadvantages:

1. We can only get a rows count, but not a physical size of table
2. This query works on one table, name of which we have to define explicitly
3. If table has no PRIMARY KEY, then this query can be very slow

There is some other method - to use a statistic from system tables.
This method uses for example this predefined SSMS report:


We can start Profiler (Menu =&rt; Tools =&rt; SQL Server Profiler) and extract for own usage a background query of this report.
I have already extracted this query and reformated as standalone VIEW:

CREATE VIEW dbo.disk_usage_by_table
AS
SELECT TOP(100) PERCENT
    Database_Name = DB_NAME(),
    Schema_Name   = CAST(a3.name AS SYSNAME),
    Table_Name    = CAST(a2.name AS SYSNAME),
    Records       = a1.records,
    Reserved_KB   = (a1.reserved + ISNULL(a4.reserved,0))* 8, 
    Data_KB       = a1.data * 8,
    Indexes_KB    = CASE
                        WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
                        THEN (a1.used + ISNULL(a4.used,0)) - a1.data
                        ELSE 0
                    END * 8,
    Unused_KB     = CASE
                        WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
                        THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
                        ELSE 0
                    END * 8
FROM
    (
        SELECT 
            ps.object_id,
            records = SUM(
                        CASE
                            WHEN (ps.index_id < 2)
                            THEN row_count
                            ELSE 0
                        END
                    ),
            reserved = SUM(ps.reserved_page_count),
            data = SUM(
                        CASE
                            WHEN ps.index_id < 2
                            THEN ps.in_row_data_page_count
                               + ps.lob_used_page_count
                               + ps.row_overflow_used_page_count
                            ELSE ps.lob_used_page_count
                               + ps.row_overflow_used_page_count
                        END
                    ),
            used = SUM(ps.used_page_count)
        FROM sys.dm_db_partition_stats ps
        GROUP BY ps.object_id
    ) AS a1
    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
    LEFT OUTER JOIN (
        SELECT 
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
        WHERE it.internal_type IN (202,204)
        GROUP BY it.parent_id
    ) AS a4 ON (a4.parent_id = a1.object_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY Table_Name;

If we query this VIEW:

SELECT * FROM dbo.disk_usage_by_table;

then we can see that [count rows], [reserved space], [space used for data], [space used for indexes] and [free space] for 106 tables takes less then 1 second:



We can also filter or join this view with other tables/views:

SELECT Records FROM dbo.disk_usage_by_table WHERE Table_Name = 'dim_date';

This method has folowing advantages:

1. Is very very fast
2. Is independent of table names
3. Shows
   • Count of records
   • Reserved space (in KBytes)
   • Space used by data (in KBytes)
   • Space used by indexes (in KBytes)
   • Unused space (in KBytes)

You can use this metod for example in ETL processes to count (outside of transformation) rows and space before and after load.

MSSQL - CHANGE TRACKING - How can I get DateTime of changes?

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications.
You can get more information on the Microsoft Developer Network web site.

But on these pages you will not find an answer on this question:
How can I get DateTime of changes. You can find this information in the sys.dm_tran_commit_table DMV (Dinamic Management View).

There the complete T-SQL script:

-- Recreate test table
IF OBJECT_ID('dbo.TestCT') IS NOT NULL
    DROP TABLE dbo.TestCT;
GO
CREATE TABLE dbo.TestCT
(
    ID INT IDENTITY(1,1) NOT NULL,
        CONSTRAINT [dbo.TestCT.PK] PRIMARY KEY CLUSTERED (ID),
    Code VARCHAR(10) NOT NULL,
    Description NVARCHAR(100) NULL
);

GO
-- Enable CHANGE TRACKING for test table
ALTER TABLE dbo.TestCT ENABLE CHANGE_TRACKING 

GO
-- Fill test table with initial rows
INSERT INTO dbo.TestCT(Code, Description)
VALUES
    ('AAA', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'),
    ('BBB', 'Cras sed tincidunt dolor, in interdum lorem.'),
    ('CCC', 'Vestibulum eget condimentum orci.');

GO
-- Wait for 30 seconds
WAITFOR DELAY '00:00:30'

GO
-- Change second row in the test table
UPDATE dbo.TestCT
SET Description = '!!! This line was changed !!!'
WHERE Code = 'BBB';

GO
-- Show CHANGE TRACKING information
-- directly after initial filling (VersionNumber=0)
SELECT
    CT.ID,
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_CREATION_VERSION,
    CT.SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, 0) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;

-- Get initialisation version number
DECLARE @MaxCreationVersion INT = (
    SELECT TOP(1) SYS_CHANGE_CREATION_VERSION
    FROM CHANGETABLE(CHANGES dbo.TestCT, 0) CT
);

-- Show changes directly after initialisation
SELECT
    CT.ID, CT.SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, @MaxCreationVersion) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;

Execution result: