How to Create Procedures In Different Databases Using Cursor

Question

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:

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.S2: I used Nesting Cursors but due to their disastrous performance, I'd appreciate other ways too!

Cheers!

Answer 1

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

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