使用将结果以文本形式显示，sp_msforeachdb批量开启和关闭变更跟踪

最新推荐文章于 2024-04-02 13:22:39 发布

原创最新推荐文章于 2024-04-02 13:22:39 发布 · 532 阅读

CC 4.0 BY-SA版权

 
exec sp_msforeachdb ' 
print ''use [?]''
print ''go''
print ''set nocount on ''
print ''select''''use master''''   ''
print ''select''''go''''     ''
print ''select''''ALTER DATABASE [?] SET CHANGE_TRACKING = ON''''    ''
print ''select ''''
USE [''''+table_catalog+'''']
GO
ALTER TABLE [''''+table_catalog+''''].[''''+table_schema+''''].[''''+table_name+''''] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON) 
'''' from INFORMATION_SCHEMA.TABLES where table_type=''''BASE TABLE''''''
print ''go''
'


exec sp_msforeachdb ' 
print ''use [?]''
print ''go''
print ''select ''''
USE [''''+table_catalog+'''']
GO
ALTER TABLE [''''+table_catalog+''''].[''''+table_schema+''''].[''''+table_name+''''] DISABLE CHANGE_TRACKING
'''' from INFORMATION_SCHEMA.TABLES where table_type=''''BASE TABLE''''''
print ''go''
print ''set nocount on ''
print ''select''''use master''''   ''
print ''select''''go''''     ''
print ''select''''ALTER DATABASE [?] SET CHANGE_TRACKING = off''''    ''
'
go


use [isoft_Avon_20170730]
go
set nocount on 
select'use master'   
select'go'     
select'ALTER DATABASE [isoft_Avon_20170730] SET CHANGE_TRACKING = ON'    
select '
USE ['+table_catalog+']
GO
ALTER TABLE ['+table_catalog+'].['+table_schema+'].['+table_name+'] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON) 
' from INFORMATION_SCHEMA.TABLES where table_type='BASE TABLE'
go

use [isoft_Avon_20170730]
go
select '
USE ['+table_catalog+']
GO
ALTER TABLE ['+table_catalog+'].['+table_schema+'].['+table_name+'] DISABLE CHANGE_TRACKING
' from INFORMATION_SCHEMA.TABLES where table_type='BASE TABLE'
go
set nocount on 
select'use master'   
select'go'     
select'ALTER DATABASE [isoft_Avon_20170730] SET CHANGE_TRACKING = off'