如何使用游标在不同的数据库中创建过程

So I'm trying to make procedures in different databases. I'm not supposed to know the name of databases. I've tried to make nesting cursors, the first one to get databases' names in dynamic way and the other one to create/alter the procedures; I used EXISTS for creating a procedures and NOT EXISTS for altering them. But somehow database sticks in 'master' and it never loops over the other ones. I know there's a problem with my inner nesting cursor, though i have no idea what this is. here's my coding:

所以我试图在不同的数据库中制作程序。我不应该知道数据库的名称。我试图制作嵌套游标,第一个用动态方式获取数据库名称,另一个用于创建/改变过程;我使用EXISTS创建过程而不是EXEST来改变它们。但不知何故,数据库仍然存在于'master'中,并且它永远不会覆盖其他数据库。我知道我的内嵌式光标存在问题,但我不知道这是什么。这是我的编码:

DECLARE GetDatabases CURSOR
FOR
    SELECT name
    FROM sys.databases
OPEN GetDatabases
DECLARE @DBName NVARCHAR(100)
DECLARE @cmd NVARCHAR(Max)

FETCH NEXT
FROM GetDatabases
INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    set @cmd='use ' + @DBName
    print @cmd
    exec sp_executesql @cmd
     FETCH NEXT
     FROM GetDatabases
     INTO @DBName
        DECLARE AutoProc CURSOR
        FOR
            SELECT TABLE_SCHEMA,TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE='BASE TABLE'
        OPEN AutoProc
        DECLARE @TableName NVARCHAR(100)
        DECLARE @TableSchema NVARCHAR(100)

        FETCH NEXT
        FROM AutoProc
        INTO @TableSchema,@TableName

        WHILE @@FETCH_STATUS = 0
        BEGIN
        IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('@TableName'))
            exec('ALTER PROCEDURE USP_SELECT_'+@TableName+' AS
            BEGIN
            SELECT *
            FROM '+@TableSchema+'.'+@TableName+'
            END ;')
        IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('@TableName'))
            exec('CREATE PROCEDURE USP_SELECT_'+@TableName+' AS
            BEGIN
            SELECT *
            FROM '+@TableSchema+'.'+@TableName+'
            END ;')
             FETCH NEXT
             FROM AutoProc
             INTO @TableSchema,@TableName
        END 
        CLOSE AutoProc
        DEALLOCATE AutoProc
END 
CLOSE GetDatabases
DEALLOCATE GetDatabases

P.S: I'm not supposed to know the name of databases because I'm trying to write a "General" procedures so it could apply to all the sql-server users' databases, not just mine.

P.S:我不应该知道数据库的名称,因为我正在尝试编写“常规”程序,因此它可以应用于所有sql-server用户的数据库,而不仅仅是我的。

P.S2: I used Nesting Cursors but due to their disastrous performance, I'd appreciate other ways too!

P.S2:我使用了Nesting Cursors,但是由于它们的灾难性表现,我也很欣赏其他方式!

Cheers!

1 个解决方案

#1

Instead of using a cursor, try using a while statement, and iterating over a temp variable table.

而不是使用游标,尝试使用while语句,并迭代临时变量表​​。

DECLARE @Databases TABLE
 (
   ID int IDENTITY(1,1),
   DatabaseName varchar(100)
 )


INSERT INTO @Databases
SELECT name FROM sys.databases

DECLARE @Idx int = (select count(*) from @Databases)

 WHILE(@Idx > 0)
   BEGIN 

    DECLARE @CurrentDatabase varchar(100) = (select DatabaseName from @Databases where @Idx = ID )

    DECLARE @SchemaData TABLE
    (
     ID int IDENTITY(1,1),
     Table_Schema varchar(20),
     Table_Name varchar(255)
    )

    DECLARE @Sql varchar(max) = 'SELECT [TABLE_SCHEMA],[TABLE_NAME] FROM [' + @CurrentDatabase + '].[INFORMATION_SCHEMA].[TABLES]'     

    INSERT INTO @SchemaData
      EXEC (@Sql)

    DECLARE @SchemaIdx int = (select count(*) from @SchemaData)

    WHILE(@SchemaIdx > 0)
      BEGIN

      DECLARE @CurrentSchema varchar(20), @CurrentTable varchar(255)

      SELECT @CurrentSchema = Table_Schema, @CurrentTable = Table_Name from      @SchemaData where ID = @SchemaIdx

      DECLARE @Sql2 varchar(max) = 
          'IF EXISTS(SELECT * FROM sys.objects WHERE type = ''P'' AND OBJECT_ID = OBJECT_ID(' + @CurrentTable + '))
          exec(''ALTER PROCEDURE USP_SELECT_'+ @CurrentTable + ' AS
          BEGIN
          SELECT *
          FROM '+ @CurrentSchema + '.'+ @CurrentTable + '
          END ;'')'

      PRINT @Sql2

      SET @SchemaIdx = @SchemaIdx - 1;
     END



  SET @Idx = @Idx - 1;
 END