如何手动删除SQL Azure数据同步对象 [英] How to remove SQL Azure Data Sync objects manually

问题描述

已经放弃了用于在两个SQL Azure数据库之间同步数据的SQL Azure数据同步，如何删除所有与DataSync相关的对象(表，触发器等)?

Having given up on SQL Azure Data Sync for synchronizing data between two SQL Azure databases, how can I remove all the DataSync related objects (tables, triggers etc)?

例如:

DataSync.<table>_dss_tracking
DataSync.schema_info_dss
DataSync.scope_config_dss
DataSync.scope_info_dss

还有其他所有对象?有可以运行的脚本吗?

And all the other objects? Is there a script that can be run?

推荐答案

msgooroo.com上有一篇文章:

There is an article on msgooroo.com:

https://msgooroo.com/GoorooTHINK/Article/15141/Removing-SQL-Azure-Sync-objects-manually/5215

基本上，脚本如下:

-- Triggers
DECLARE @TRIGGERS_SQL VARCHAR(MAX) = (
    SELECT
        'DROP TRIGGER [' + SCHEMA_NAME(so.uid) + '].[' +  [so].[name] + '] ' 
        FROM sysobjects AS [so]
        INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
        WHERE   [so].[type] = 'TR'
        AND     [so].name LIKE '%_dss_%_trigger'
    FOR XML PATH ('')
)
PRINT @TRIGGERS_SQL
IF LEN(@TRIGGERS_SQL) > 0
BEGIN
    EXEC (@TRIGGERS_SQL)
END     


-- Tables
DECLARE @TABLES_SQL VARCHAR(MAX) = (
    SELECT
        'DROP TABLE [' + table_schema + '].[' + table_name + '] ' 
    FROM 
        information_schema.tables where table_schema = 'DataSync' 
    FOR XML PATH ('')
)
PRINT @TABLES_SQL
IF LEN(@TABLES_SQL) > 0
BEGIN
    EXEC (@TABLES_SQL)
END    

-- Stored Procedures
DECLARE @PROC_SQL VARCHAR(MAX) = (
    SELECT 'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + '] ' 
    FROM    INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'DataSync' and routine_type = 'PROCEDURE'
    FOR XML PATH ('')
)
PRINT @PROC_SQL
IF LEN(@PROC_SQL) > 0
BEGIN
    EXEC (@PROC_SQL)
END    


-- Types
DECLARE @TYPE_SQL VARCHAR(MAX) = (
  SELECT
  'DROP TYPE [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' 
  FROM systypes AS [so]
  where [so].name LIKE '%_dss_bulktype%'
  AND SCHEMA_NAME(so.uid) = 'Datasync'
  FOR XML PATH ('')
)
PRINT @TYPE_SQL
IF LEN(@TYPE_SQL) > 0
BEGIN
  EXEC (@TYPE_SQL)
END    


-- Schema
DROP SCHEMA DataSync    

这篇关于如何手动删除SQL Azure数据同步对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！