Setting up the bottles of beer

A lot of software engineering is just glueing the pieces togethers.

This scripts can run sql on multiple databases that use a consistent numeric naming structure.

This code is ripped from all over and there is nothing original except maybe a print

The script

The pieces this script is using…

Gets 1 to 20 using a with.
Use a cursor to iterate.
quotename to confirm the database name.
sq_sqlexec to run it, because the use requires an actual string.

DECLARE @index nvarchar(2);
DECLARE @sql nvarchar(4000);
DECLARE @start int;
DECLARE @end int;
DECLARE @dbName sysname;

SET @start = 1;
SET @end = 20;


DECLARE cur CURSOR STATIC LOCAL FOR
    WITH numbers AS (
	SELECT @start AS number
	UNION ALL
	SELECT number + 1
	FROM numbers
	WHERE number < @end
    )
    SELECT REPLACE(STR(Number, 2), SPACE(1), '0')
    FROM numbers
OPEN cur

WHILE 1 = 1
BEGIN
    FETCH cur INTO @index
    IF @@fetch_status <> 0
	BREAK

    PRINT @index + ' bottles of beer on the wall.';

    SET @dbName = 'database-number-' + @index
    SET @sql = N'
USE ' + quotename(@dbName) + ';
SELECT db_name();
';
    EXECUTE sp_executesql @sql;
END

DEALLOCATE cur