Scenario:
You are working as SQL Server DBA or Developer, you need to write script that you can use to enable Change Data Capture (CDC ) on all the tables in a database or Enable on some specific Tables.
The below script can be used to Enable Change Data Capture on all the table in a database of selected tables. You can change the query to include or exclude the tables you like.
/*------------------------------------------------ Enable CDC on Set of Tables ( To Enable CDC on Table , CDC Should be enabled on Database level) --------------------------------------------------*/ USE [DatabaseName] GODECLARE @TableName VARCHAR(100)DECLARE @TableSchema VARCHAR(100)DECLARE CDC_Cursor CURSOR FOR SELECT * FROM ( SELECT 'T' AS TableName,'dbo' AS TableSchema UNION ALL SELECT 'T2' AS TableName,'dbo' AS TableSchema --IF want to Enable CDC on All Table, then use --SELECT Name,SCHEMA_NAME(schema_id) AS TableSchema --FROM sys.objects --WHERE type = 'u' -- AND is_ms_shipped <> 1 ) CDCOPEN CDC_CursorFETCH NEXT FROM CDC_Cursor INTO @TableName,@TableSchemaWHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL NVARCHAR(1000) DECLARE @CDC_Status TINYINT SET @CDC_Status=(SELECT COUNT(*) FROM cdc.change_tables WHERE Source_object_id = OBJECT_ID(@TableSchema+'.'+@TableName)) --IF CDC Already Enabled on Table , Print Message IF @CDC_Status = 1 PRINT 'CDC is already enabled on ' +@TableSchema+'.'+@TableName + ' Table' --IF CDC is not enabled on Table, Enable CDC and Print Message IF @CDC_Status <> 1 BEGIN SET @SQL='EXEC sys.sp_cdc_enable_table @source_schema = '''+@TableSchema+''', @source_name = ''' + @TableName + ''', @role_name = null;' EXEC sp_executesql @SQL PRINT 'CDC enabled on ' +@TableSchema+'.'+ @TableName + ' Table successfully' END FETCH NEXT FROM CDC_Cursor INTO @TableName,@TableSchema END CLOSE CDC_CursorDEALLOCATE CDC_Cursor
Liên hệ Aivivu, đặt vé máy bay tham khảo
ReplyDeletemua ve may bay di my
giá vé máy bay sài gòn quy nhơn
giá vé máy bay từ thanh hóa đi tphcm
vé máy bay đi hà nội bamboo
giá vé từ mỹ về việt nam
taxi sân bay nội bài hà nội
combo vinpearl nha trang 3 ngày 2 đêm 2021