I have a script with 200+ commands and want to schedule the script to run overnight via sqlcmd against any database I specify. Passing databasename to the script is straightforward but how to then ensure that USE databasename will hold for every command.

The approach I came up with is to create a temporary procedure at the beginning of the script that I can then repeatedly call passing @databasename and @sql. The script below demonstrates the approach.

/* example script */
IF OBJECT_ID('tempdb..#sp_querydb') IS NOT NULL
    DROP PROCEDURE #sp_querydb
GO
CREATE PROCEDURE #sp_querydb @db nvarchar(100), @query varchar(500)
AS
  BEGIN
    SET NOCOUNT ON
    DECLARE @sql varchar(550)
    SELECT @sql = 'USE ' + @db + '; ' + @query + ';'
    EXEC sp_executesql @sql  -- or sp_sqlexec
  END
GO
EXEC #sp_querydb 'mydb', 'SELECT DB_NAME()'
GO
EXEC #sp_querydb 'myotherdb', 'SELECT DB_NAME()'
GO

/* OUTPUT */
-------------
mydb
-------------
myotherdb