SQLDBA- How to verify the CDC is enabled data bases or tables

How to  verify the CDC is enabled data bases or tables

The following query  will get list  of  CDC enabled data bases 

SELECT

    name AS DatabaseName,

    is_cdc_enabled

FROM sys.databases

WHERE is_cdc_enabled = 1;

The following query  will get list  of  CDC enabled  tables  with in the  database. 

USE <DB Name>;  -- Replace with actual DB name

GO

SELECT 

    s.name AS SchemaName,

    t.name AS TableName,

    c.capture_instance,

    c.supports_net_changes,

    c.start_lsn,

    c.end_lsn

FROM 

    cdc.change_tables c

JOIN 

    sys.tables t ON c.source_object_id = t.object_id

JOIN 

    sys.schemas s ON t.schema_id = s.schema_id;

Below will get the list of tables which  are enabled CDC from all data bases. 

DECLARE @dbName NVARCHAR(128)

DECLARE @sql NVARCHAR(MAX)

-- Cursor to loop through all CDC-enabled databases

DECLARE db_cursor CURSOR FOR

SELECT name FROM sys.databases WHERE is_cdc_enabled = 1

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT 'Checking CDC tables in database: ' + @dbName

    SET @sql = '

    USE [' + @dbName + '];

    IF EXISTS (SELECT 1 FROM sys.tables WHERE is_tracked_by_cdc = 1)

    BEGIN

        SELECT 

            ''' + @dbName + ''' AS DatabaseName,

            s.name AS SchemaName,

            t.name AS TableName,

            c.capture_instance,

            c.supports_net_changes,

            c.start_lsn,

            c.end_lsn

        FROM 

            cdc.change_tables c

        JOIN 

            sys.tables t ON c.source_object_id = t.object_id

        JOIN 

            sys.schemas s ON t.schema_id = s.schema_id;

    END

    ELSE

    BEGIN

        PRINT ''No CDC-enabled tables found in database: ' + @dbName + '''

    END

    '

    EXEC sp_executesql @sql

    FETCH NEXT FROM db_cursor INTO @dbName

END

CLOSE db_cursor

DEALLOCATE db_cursor