sql - 如何手动删除 SQL Azure 数据同步对象

Question

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

例如:

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

和所有其他对象？有没有可以运行的脚本？

Answer 1

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