为什么drop和create synonym不断重复

DECLARE @synName VARCHAR(100), @synTarget VARCHAR(150), @synSchemaName VARCHAR (110)

DECLARE Syns CURSOR FOR
   SELECT 
      s.name, base_object_name, sc.name AS synSchemaName
   FROM 
      sys.synonyms s
   INNER JOIN 
      sys.schemas sc ON s.schema_id = sc.schema_id
   WHERE 
      s.name in ('test1') 

   OPEN Syns
   FETCH NEXT FROM Syns INTO @synName, @synTarget, @synSchemaName

   WHILE @@FETCH_STATUS <> -1
   BEGIN
      PRINT 'DROP SYNONYM guest.' + @synName
      EXEC ('DROP SYNONYM guest.' + @synName) 

      SET @synTarget = CASE 
                          WHEN @synTarget LIKE '%test2%' THEN REPLACE(@synTarget,'[test2]','[test3]')
                          WHEN @synTarget LIKE '%test3%' THEN REPLACE(@synTarget,'[test3]','[test4]')
                          WHEN @synTarget LIKE '%test4%' THEN REPLACE(@synTarget,'[test4]','[test2]')
                       END

     PRINT 'CREATE SYNONYM guest.' + @synName + ' FOR ' +@synTarget
     EXEC ('CREATE SYNONYM guest.' + @synName + ' FOR ' +@synTarget)

     PRINT 'TRUNCATE TABLE'+@syntarget
     --EXEC ('TRUNCATE TABLE'+@syntarget)

     FETCH NEXT FROM Syns INTO @synName, @synTarget, @synSchemaName
  END

  CLOSE Syns
  DEALLOCATE Syns

I am working with the above code that I have in another post. I ran into a new issue after running the query. Instead of it just doing this once.

我正在使用上面的代码,我在另一篇文章中。运行查询后,我遇到了一个新问题。而不只是这样做一次。

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

It will do this instead

它会改为做到这一点

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test2]
TRUNCATE TABLE[master].[dbo].[test2]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test3]
TRUNCATE TABLE[master].[dbo].[test3]

DROP SYNONYM guest.test1
CREATE SYNONYM guest.test1 FOR [master].[dbo].[test4]
TRUNCATE TABLE[master].[dbo].[test4]
..... and so on ......

When I take out both the EXEC commands from the drop and create it only shows once. But nothing is actually being done. When I use the EXEC for the DROP only and comment out the other ones it drops the synonym and shows it once. But once I include the EXEC for the create it starts repeating like crazy and clears all the tables.

当我从drop中取出两个EXEC命令并创建它时只显示一次。但实际上并没有做任何事情。当我仅使用EXEC进行DROP并注释掉其他的时,它会丢弃同义词并显示一次。但是,一旦我将EXEC包含在创建中,它就会像疯了一样重复并清除所有表格。

I have removed the braces around test2 and everything to see if that works and am still having the same issue.

我已经删除了test2周围的大括号以及所有内容,看看它是否有效并且仍然存在同样的问题。

I am lost on why it does this and how to fix it so it only goes through one time and then stops.

我迷失了它为什么这样做以及如何修复它所以它只经过一次然后停止。

1 个解决方案

#1

The changes you are doing in the loop affect the system views your cursor is reading. Your cursor also "sees", and reflects, them. This is the default behaviour, which can be changed by the STATIC option:

您在循环中所做的更改会影响光标正在读取的系统视图。你的光标也“看到”并反映它们。这是默认行为,可以通过STATIC选项更改:

DECLARE Syns CURSOR STATIC FOR
   SELECT
   ...

The STATIC option in a cursor declaration causes the cursor to create and use a temporary copy of the data obtained from the underlying tables/views. This way any changes to those datasets will not be not reflected in the cursor.

游标声明中的STATIC选项使游标创建并使用从基础表/视图获取的数据的临时副本。这样,对这些数据集的任何更改都不会反映在游标中。

More information about this and other cursor options can be found in the manual page:

有关此选项和其他光标选项的更多信息,请参见手册页:

DECLARE CURSOR (Transact-SQL)

DECLARE CURSOR(Transact-SQL)

#1