假设在 SQL Server 数据库上启用了更改跟踪，如何在数据库中的所有表上启用更改跟踪？
您可以使用以下 T-SQL 脚本生成另一个 T-SQL 脚本，该脚本启用CHANGE TRACKING所有具有主键的表的功能：
-- 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），粘贴并执行此脚本。
结果选项卡：
Enable CHANGE_TRACKING on [dbo].[Documents]
Enable CHANGE_TRACKING on [dbo].[Events]
Enable CHANGE_TRACKING on [dbo].[Names]
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
