当前位置： 文江博客话题详情

Microsoft Sync Framework - 如何在架构更改后重新配置表（或整个范围）？

发布于 2024-09-03 03:40:14 字数 387 浏览 5 评论 0原文

我已经设置了与 Microsoft Sync Framework 同步，现在我需要向表中添加字段。 如何重新配置​​数据库？

设置非常简单：

  • 两台 SQL Express 2008 服务器
  • 范围包括整个数据库
  • 使用 Microsoft Sync Framework 2.0
  • 通过直接访问进行同步。使用标准的新 SqlSyncProvider

我是否在两端进行结构更改？或者我是否只更改一台服务器并让 Sync Framework 以某种方式传播更改？

我是否需要删除_tracking 表和/或存储过程？触发器怎么样？

有人用过同步框架吗？请帮忙。

原文

I have already setup syncing with Microsoft Sync Framework, and now I need to add fields to a table.
How do I re-provision the databases?

The setup is exceedingly simple:

  • Two SQL Express 2008 servers
  • The scope includes the entire database
  • Using Microsoft Sync Framework 2.0
  • Synchronizing by direct access. Using the standard new SqlSyncProvider

Do I make the structural changes at both ends? Or do I only change one server and let Sync Framework somehow propagate the change?

Do I need to delete the _tracking tables and/or the stored procedures? How about the triggers?

Has anyone been using the Sync Framework? Please help.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

离线来电— 2024-09-10 03:40:14

实际上，我在我的博客上发布了自己的答案 http://myazurejourney.blogspot.com/

它有一个几步，这绝对是一个黑客。但它有效。

一探究竟。告诉我你的想法

Actually, I posted my own answer on my blog http://myazurejourney.blogspot.com/

It has a few step, and it's definitely a hack. But it works.

check it out. tell me what you think

回复 原文
伴我老 2024-09-10 03:40:14

我使用了 Montago 的解决方案，但将其转换为 SQL 代码，这对我来说完成了这项工作。随意使用它，我希望它对您有所帮助:)

USE DatabaseName
GO

DECLARE @sqlDrop VARCHAR(1000)
SET @sqlDrop = 'IF EXISTS (SELECT * FROM #table# WHERE object_id = OBJECT_ID(''#name#'')) DROP #what# #name#'
DECLARE @sqlCommand VARCHAR(1000)

DECLARE @id INT
SET @id = 0
DECLARE @name SYSNAME
DECLARE @prev INT

WHILE 1 = 1
   BEGIN
      /* find traces of synchronization */

      -- to be sure that id changed
      SET @prev = @id

      -- get the next table
      SELECT TOP 1
             @id = object_id,
             @name = name
        FROM sys.tables
       WHERE object_id > @id
       ORDER BY object_id

      -- confirm that there is next table
      IF @id = @prev
         BREAK

      /* remove traces of synchronization */

      -- remove table
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_tracking')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
      EXEC (@sqlCommand)

      -- remove triggers
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.triggers')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete_trigger')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TRIGGER')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete_trigger', '_insert_trigger')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert_trigger', '_update_trigger')
      EXEC (@sqlCommand)

      -- remove stored procedures
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.procedures')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'PROCEDURE')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete', '_deletemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_deletemetadata', '_insert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert', '_insertmetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insertmetadata', '_selectchanges')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectchanges', '_selectrow')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectrow', '_update')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_update', '_updatemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_updatemetadata', '_bulkdelete')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkdelete', '_bulkinsert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkinsert', '_bulkupdate')
      EXEC (@sqlCommand)

   END

-- remove scope and schema tables
SET @sqlCommand = @sqlDrop
SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
SET @sqlCommand = REPLACE(@sqlCommand, '#name#', 'schema_info')
SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'schema_info', 'scope_config')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'scope_config', 'scope_info')
EXEC (@sqlCommand)

正如您所看到的，它会遍历所有表格并尝试查找同步痕迹。您只需更改数据库名称（第一行）。
另外，如果您希望删除的内容更安全，请使用以下代码查找表：

  -- get the next table
  SELECT TOP 1
         @id = object_id,
         @name = REPLACE(name, '_tracking', '')
    FROM sys.tables
   WHERE object_id > @id
     AND name LIKE '%_tracking'
   ORDER BY object_id

这只会查找实际正在同步的表

I've used Montago's solution, but converted it to SQL code which did the job for me. Be free to use it, I hope it helps you :)

USE DatabaseName
GO

DECLARE @sqlDrop VARCHAR(1000)
SET @sqlDrop = 'IF EXISTS (SELECT * FROM #table# WHERE object_id = OBJECT_ID(''#name#'')) DROP #what# #name#'
DECLARE @sqlCommand VARCHAR(1000)

DECLARE @id INT
SET @id = 0
DECLARE @name SYSNAME
DECLARE @prev INT

WHILE 1 = 1
   BEGIN
      /* find traces of synchronization */

      -- to be sure that id changed
      SET @prev = @id

      -- get the next table
      SELECT TOP 1
             @id = object_id,
             @name = name
        FROM sys.tables
       WHERE object_id > @id
       ORDER BY object_id

      -- confirm that there is next table
      IF @id = @prev
         BREAK

      /* remove traces of synchronization */

      -- remove table
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_tracking')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
      EXEC (@sqlCommand)

      -- remove triggers
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.triggers')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete_trigger')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TRIGGER')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete_trigger', '_insert_trigger')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert_trigger', '_update_trigger')
      EXEC (@sqlCommand)

      -- remove stored procedures
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.procedures')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'PROCEDURE')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete', '_deletemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_deletemetadata', '_insert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert', '_insertmetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insertmetadata', '_selectchanges')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectchanges', '_selectrow')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectrow', '_update')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_update', '_updatemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_updatemetadata', '_bulkdelete')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkdelete', '_bulkinsert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkinsert', '_bulkupdate')
      EXEC (@sqlCommand)

   END

-- remove scope and schema tables
SET @sqlCommand = @sqlDrop
SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
SET @sqlCommand = REPLACE(@sqlCommand, '#name#', 'schema_info')
SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'schema_info', 'scope_config')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'scope_config', 'scope_info')
EXEC (@sqlCommand)

This, as you can see goes through all the tables and try to find traces of syncing. You just need to change the database name (first line).
Also, if you want to be safer with what you delete, use this code to find tables:

  -- get the next table
  SELECT TOP 1
         @id = object_id,
         @name = REPLACE(name, '_tracking', '')
    FROM sys.tables
   WHERE object_id > @id
     AND name LIKE '%_tracking'
   ORDER BY object_id

This will only look for tables that are actually being synchronized

回复 原文
人生戏 2024-09-10 03:40:14

删除范围并重新配置...使用此代码删除范围

private void RemoveScope(SqlConnection Conn, bool ShowAlert)
{
    foreach (var table in _settings.TablesToSync)
    {
        SqlCommand dropTracking = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_tracking]') AND type in (N'U'))
            DROP TABLE [dbo].[" + table.Key + "_tracking]", Conn);
        dropTracking.ExecuteNonQuery();

        SqlCommand dropTriggers = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_delete_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_insert_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_update_trigger];
        ", Conn);
        dropTriggers.ExecuteNonQuery();

        SqlCommand dropStoredProc = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_delete];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_deletemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_deletemetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insert];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insertmetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insertmetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectchanges]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectchanges];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectrow]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectrow];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_update];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_updatemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_updatemetadata];
        ", Conn);
        dropStoredProc.ExecuteNonQuery();
    }

    SqlCommand getScopeGuid = new SqlCommand(@"
            USE [" + Conn.Database + @"]
            SELECT scope_config_id FROM scope_info WHERE scope_name = '" + _settings.ScopeName + "'", Conn);

    var reader = getScopeGuid.ExecuteReader();

    if (reader.HasRows)
    {
        reader.Read();

        var id = reader.GetGuid(0);

        reader.Close();

        SqlCommand deleteScope = new SqlCommand(@"
                DELETE FROM scope_info WHERE scope_config_id = '" + id + @"';
                DELETE FROM scope_config WHERE config_id = '" + id + @"';
            ", Conn);
        deleteScope.ExecuteNonQuery();
    }

    if(ShowAlert)
        MessageBox.Show("Scope has been removed");
}

remove the scope and re-provision... use this code to remove a scope

private void RemoveScope(SqlConnection Conn, bool ShowAlert)
{
    foreach (var table in _settings.TablesToSync)
    {
        SqlCommand dropTracking = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_tracking]') AND type in (N'U'))
            DROP TABLE [dbo].[" + table.Key + "_tracking]", Conn);
        dropTracking.ExecuteNonQuery();

        SqlCommand dropTriggers = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_delete_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_insert_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_update_trigger];
        ", Conn);
        dropTriggers.ExecuteNonQuery();

        SqlCommand dropStoredProc = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_delete];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_deletemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_deletemetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insert];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insertmetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insertmetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectchanges]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectchanges];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectrow]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectrow];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_update];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_updatemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_updatemetadata];
        ", Conn);
        dropStoredProc.ExecuteNonQuery();
    }

    SqlCommand getScopeGuid = new SqlCommand(@"
            USE [" + Conn.Database + @"]
            SELECT scope_config_id FROM scope_info WHERE scope_name = '" + _settings.ScopeName + "'", Conn);

    var reader = getScopeGuid.ExecuteReader();

    if (reader.HasRows)
    {
        reader.Read();

        var id = reader.GetGuid(0);

        reader.Close();

        SqlCommand deleteScope = new SqlCommand(@"
                DELETE FROM scope_info WHERE scope_config_id = '" + id + @"';
                DELETE FROM scope_config WHERE config_id = '" + id + @"';
            ", Conn);
        deleteScope.ExecuteNonQuery();
    }

    if(ShowAlert)
        MessageBox.Show("Scope has been removed");
}
回复 原文
拥醉 2024-09-10 03:40:14

您可以使用这个脚本：

declare @SQL varchar(max);
set @SQL='';
select @SQL += ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[schema_info]'') AND type in (N''U'')) ' +  
               ' DROP TABLE [dbo].[schema_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_config]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_config]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_info]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' +  name  + '_tracking]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[' +  name  + '_tracking]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_delete_trigger];' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_insert_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_update_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_delete]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_deletemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_deletemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insert]; ' + 
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insertmetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insertmetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectchanges]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectchanges]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectrow]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectrow]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_update]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_updatemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_updatemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_BulkType]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_BulkType]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkinsert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkinsert]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkupdate]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkupdate]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkdelete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkdelete]; '              
from sysobjects
where type = 'U'

--select @SQL
EXEC(@SQL)
GO

You can use this script:

declare @SQL varchar(max);
set @SQL='';
select @SQL += ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[schema_info]'') AND type in (N''U'')) ' +  
               ' DROP TABLE [dbo].[schema_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_config]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_config]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_info]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' +  name  + '_tracking]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[' +  name  + '_tracking]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_delete_trigger];' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_insert_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_update_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_delete]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_deletemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_deletemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insert]; ' + 
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insertmetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insertmetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectchanges]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectchanges]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectrow]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectrow]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_update]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_updatemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_updatemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_BulkType]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_BulkType]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkinsert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkinsert]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkupdate]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkupdate]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkdelete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkdelete]; '              
from sysobjects
where type = 'U'

--select @SQL
EXEC(@SQL)
GO
回复 原文
~没有更多了~

关于作者

潇烟暮雨

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

玍銹的英雄夢

文章 0 评论 0

我不会写诗

文章 0 评论 0

十六岁半

文章 0 评论 0

浸婚纱

文章 0 评论 0

qq_kJ6XkX

文章 0 评论 0

旧伤还要旧人安

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文