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

DBA- Server Login name , DB login name along with their roles/memebers in sql server

 

Below Query  help  bring the  server  login name , data base level login name  along with their roles/members. 

This query will help us to validate the roles/ members are mapped correctly or not. 

USE master 

GO

CREATE TABLE #temp

(

id INTEGER  IDENTITY(1,1),

DatabasseName VARCHAR(100),

login_name VARCHAR(500),

database_user VARCHAR(500),

role_name VARCHAR(500),

user_type VARCHAR(500),

authentication_type_desc VARCHAR(500)

)

EXEC sp_foreachdb 

'

USE ?

INSERT INTO #temp (DatabasseName,login_name,database_user,role_name,user_type,authentication_type_desc)

SELECT

''?'' AS DatabasseName,

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

 JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN (''S'',''U'', ''G'') AND dp.type IN (''S'', ''U'', ''G'')

ORDER BY 

    sp.name, dr.name 

'

SELECT  *  FROM #temp

WHERE login_name ='svcTMWorks-trng' 

--- DB Login names with their roles

SELECT

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

LEFT JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN ('S', 'U', 'G') AND dp.type IN ('S', 'U', 'G')

AND sp.name  ='svcTMWorks-trng'

ORDER BY 

    sp.name, dr.name;

SQLDBA- how to know the created index file group name with T-SQL

 Hello, 

To determine the filegroup where an index is created in SQL Server, you can use the following methods:

1. Using sys.indexes and sys.filegroups System Views

You can query the system views to find the filegroup of an index:

Sql
SELECT 
    i.name AS IndexName,
    o.name AS TableName,
    f.name AS FileGroupName
FROM 
    sys.indexes i
JOIN 
    sys.objects o ON i.object_id = o.object_id
JOIN 
    sys.data_spaces f ON i.data_space_id = f.data_space_id
WHERE 
    o.type = 'U'; -- Only user tables

This query will list all indexes, their associated tables, and the filegroups they belong to.

2. Using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to your database.
2. Navigate to the table where the index is created.
3. Expand the Indexes folder under the table.
4. Right-click the index and select Properties.
5. In the Storage section, you can see the filegroup where the index is stored.

3. Using sp_helpindex

You can use the sp_helpindex system stored procedure to get details about indexes on a specific table:

Sql
EXEC sp_helpindex 'YourTableName';

This will return information about the indexes on the table, including their names and types. However, it does not directly show the filegroup. For filegroup details, use the first method.