How to enable change tracking on a database without explicitly using its name?

Question

I'm currently having this script that enables change tracking on one table:

IF (SELECT COUNT(*) FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID('TB_Styles')) = 0
Begin
    ALTER TABLE [dbo].[TB_Styles] ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)
End

It was working on different databases till one database executed with an error stating that change tracking must be enabled on the database first, I wasn't aware of that condition (it's my first deal with change tracking in SQL), so I added this script before the previous script:

IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=DB_ID('TailorsDb')) = 0
Begin
ALTER DATABASE TailorsDb
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
End

it's working, but the problem is that I'm explicitly using the database name (in IF and ALTER statements), which is not good because these scripts are run on different machines and the database name is not guaranteed to be the same.

(*I'm using IF statement to avoid errors when script is run more than one time accidentally)

Answer 1

Change your code to use system function B_NAME() instead of hardcoding the database name.

Declare @DBNAME SYSNAME = DB_NAME()
      , @Sql    NVARCHAR(MAX);

IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=DB_ID(@DBNAME)) = 0
Begin
    SET @Sql = N'   ALTER DATABASE '+  QUOTENAME(@DBNAME) + N'
                    SET CHANGE_TRACKING = ON  
                    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) '

    Exec sp_executesql  @Sql
End


IF (SELECT COUNT(*) FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID('TB_Styles')) = 0
Begin
    ALTER TABLE [dbo].[TB_Styles] ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)
En

Also instead of using count(*) = 0 I would use something like Exists operator, it is much cleaner.

Declare @DBNAME SYSNAME = DB_NAME()
      , @Sql    NVARCHAR(MAX);


IF NOT EXISTS (SELECT 1 
               FROM sys.change_tracking_databases 
               WHERE database_id = DB_ID(@DBNAME))
Begin
    SET @Sql = N'   ALTER DATABASE '+  QUOTENAME(@DBNAME) + N'
                    SET CHANGE_TRACKING = ON  
                    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) '

    Exec sp_executesql  @Sql
End


IF NOT EXISTS ( SELECT 1
                FROM sys.change_tracking_tables 
                WHERE object_id = OBJECT_ID('TB_Styles'))
Begin
    ALTER TABLE [dbo].[TB_Styles] 
        ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)
End