对数据库中的所有表启用更改跟踪 [英] Enable change tracking on all tables in database

问题描述

假设在SQL Server数据库上启用了更改跟踪，如何在数据库中的所有表上启用更改跟踪?

Assuming change tracking is enabled on a SQL Server database, how do I enable change tracking on all the tables in the database?

推荐答案

您可以使用以下T-SQL脚本生成另一个T-SQL脚本，该脚本在具有主键的所有表上启用CHANGE TRACKING功能:

You could use following T-SQL script to generate another T-SQL script which enable CHANGE TRACKING feature on all tables with primary keys:

-- Step #1: Execute below script having [Results to text] option selected (Ctrl + T)
SET NOCOUNT ON;
GO    

-- Is CHANGE TRACKING enabled at database level ?
IF CONVERT(INT, PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion')), 4)) >= 10 -- 10 = SQL2008
BEGIN
    EXEC sp_executesql N'SELECT * FROM sys.change_tracking_databases db WHERE db.database_id = DB_ID(); ' -- sys.change_tracking_databases is available in SQL2008+
    IF @@ROWCOUNT = 0
    BEGIN 
        RAISERROR('CHANGE TRACKING is not enabled at database level.', 16, 1);
        RETURN;
    END
END

-- It generates the final T-SQL script
SELECT    N'PRINT ''Enable CHANGE_TRACKING on ' + full_table_name + ''';'
        + N'ALTER TABLE ' + full_table_name + N' ENABLE CHANGE_TRACKING' + CHAR(13) + CHAR(10) 
        + N'GO'
FROM (
    SELECT  QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS full_table_name,
            s.name AS schema_name, t.name AS table_name
    FROM    sys.key_constraints x
    JOIN    sys.tables t ON x.parent_object_id = t.object_id
    JOIN    sys.schemas s ON t.schema_id = s.schema_id 
    WHERE   x.[type] = 'PK'
) y
ORDER BY schema_name, table_name

结果"标签:

PRINT 'Enable CHANGE_TRACKING on [dbo].[Documents]';ALTER TABLE [dbo].[Documents] ENABLE CHANGE_TRACKING
GO
PRINT 'Enable CHANGE_TRACKING on [dbo].[Events]';ALTER TABLE [dbo].[Events] ENABLE CHANGE_TRACKING
GO
PRINT 'Enable CHANGE_TRACKING on [dbo].[Names]';ALTER TABLE [dbo].[Names] ENABLE CHANGE_TRACKING
GO

步骤2:从[结果]标签中选择所有文本，复制文本，打开新的查询窗口(Ctrl + N)，粘贴并执行此脚本.

Step #2: select all text from [Results] tab, copy text, open a new query window (Ctrl + N), paste and execute this script.

结果"标签:

Enable CHANGE_TRACKING on [dbo].[Documents]
Enable CHANGE_TRACKING on [dbo].[Events]
Enable CHANGE_TRACKING on [dbo].[Names]

这篇关于对数据库中的所有表启用更改跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！