USE DBAdmin
GO
	
IF OBJECT_ID ( 'usp_MaintainDBs', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_MaintainDBs;
GO

CREATE PROCEDURE usp_MaintainDBs @batchNo SMALLINT
AS
DECLARE @dbId INT
DECLARE @dbName VARCHAR(100)
DECLARE @sql VARCHAR(1000)

-- Check table to hold database list exists
IF OBJECT_ID('dbo.DbsToMaintain', 'U') IS NULL
BEGIN
	
	CREATE TABLE DbsToMaintain
	(
		DatabaseID INT NOT NULL,
		DatabaseName VARCHAR(100) NOT NULL,
		LastDateMaintained SMALLDATETIME NULL
	)
END
-- Check if every database has been processed. If so, reset the date fields to NULL
IF (SELECT COUNT(*) FROM DbsToMaintain WHERE LastDateMaintained IS NULL) = 0
BEGIN
	UPDATE DbsToMaintain SET LastDateMaintained = NULL
END

-- Get all of the databases to be included in the maintenance routine and check for newly created ones
INSERT INTO DbsToMaintain
SELECT sd.database_id, sd.name, NULL 
FROM sys.databases sd
LEFT OUTER JOIN DbsToMaintain dtm ON dtm.databaseid = sd.database_id
WHERE sd.database_id NOT IN (1, 2, 3, 4)
AND dtm.DatabaseID IS NULL
ORDER BY database_id

-- Remove any databases form the table that no longer reside on the server
DELETE FROM DbsToMaintain
WHERE DatabaseID NOT IN (SELECT database_id FROM sys.databases)

-- Create a cursor to loop through the databases
SET @sql = 'DECLARE db_Cursor CURSOR FOR'
	+ ' SELECT TOP ' + CONVERT(VARCHAR, @batchNo) + ' DatabaseID, DatabaseName FROM DbsToMaintain WHERE LastDateMaintained IS NULL'
	+ ' OPEN db_Cursor'
	+ ' FETCH NEXT FROM db_Cursor INTO ' + CONVERT(VARCHAR, @dbId) + ', ' + @dbName
	+ ' WHILE @@FETCH_STATUS = 0'
	+ ' BEGIN'
	+ ' UPDATE DbsToMaintain SET LastDateMaintained = GETDATE() WHERE DatabaseID = ' + CONVERT(VARCHAR, @dbId)
	+ ' PRINT ' + @dbName + ' processed'
	+ ' FETCH NEXT FROM db_Cursor INTO ' + CONVERT(VARCHAR, @dbId) + ', ' + @dbName
	+ ' END'	
	+ ' CLOSE db_Cursor'
	+ ' DEALLOCATE db_Cursor'
EXEC(@sql)
go

declare @batchNo int
DECLARE @dbId INT
DECLARE @dbName VARCHAR(100)
set	@batchNo = 2
set	@dbId = 5
set	@dbName = 'test'

declare @sql varchar(max)
SET @sql = 'DECLARE db_Cursor CURSOR FOR'
	+ ' SELECT TOP ' + CONVERT(VARCHAR, @batchNo) + ' DatabaseID, DatabaseName FROM DbsToMaintain WHERE LastDateMaintained IS NULL'
	+ ' OPEN db_Cursor'
	+ ' FETCH NEXT FROM db_Cursor INTO ' + CONVERT(VARCHAR, @dbId) + ', ' + @dbName
	+ ' WHILE @@FETCH_STATUS = 0'
	+ ' BEGIN'
	+ ' UPDATE DbsToMaintain SET LastDateMaintained = GETDATE() WHERE DatabaseID = ' + CONVERT(VARCHAR, @dbId)
	+ ' PRINT ' + @dbName + ' processed'
	+ ' FETCH NEXT FROM db_Cursor INTO ' + CONVERT(VARCHAR, @dbId) + ', ' + @dbName
	+ ' END'	
	+ ' CLOSE db_Cursor'
	+ ' DEALLOCATE db_Cursor'
print(@sql)