This posting shows one approach for the lookup of stored procedure parameter values. The example use case will be the very popular SQL Server Maintenance Solution authored by Ola Hallengren (https://ola.hallengren.com/).
The solution consists of three parameterised stored procedures, one for database backups, one for database integrity checks and one for index optimisations. These procedures can be run as SQL Agent TSQL job steps. The stored procedure calls along with their parameter values can be encoded in these job steps. But maintenance can become a bit of a problem.
Suppose you have many databases on your SQL Server. You have multiple backup job steps with all but one of the backup stored procedure parameters being different. They differ in terms of which directory they write their backups to. Suppose you are low on storage so a new volume has been assigned. You now need to identify and update those job steps that are referring to the close to full volumes and change the directory name parameter to the newly presented volume. This can be done manually using the GUI or you could hack the msdb.dbo.sysjobsteps table updating the command column. As another example suppose you have multiple job stops implementing different index optimisation parameters. Again you could refer to the sysjobsteps table.
A far better approach would be to store the parameter values in a lookup table. You would have three lookup tables, one for each of the maintenance operations – backup, integrity check and index optimisation. You list all the variations of the parameters as separate rows in these three tables with each row having a descriptive tag name. This tag name column be the primary key for that table. So a given tag name would refer to all the parameter values for an execution of that operation.
Taking the dbo.DatabaseBackup stored procedure as an example these are the parameters (as of the time of writing):
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_CATALOG = db_name() AND SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = 'DatabaseBackup' ORDER BY ORDINAL_POSITION
The screenshot doesn’t show the complete list of parameters. There are 36 in total for this procedure. With the exception of @Databases (which will be dealt with separately later) we want to pivot so that PARAMETER_NAME values (with the leading @ symbol ignored) become our column names. In addition, the first column name will be named ParamTag and be the primary key. The second column will be a fixed value, the stored procedure name. Here’s a procedure that accomplishes this task:
/* First create a common 'parent' table for all three procedures. Storing the ParamTag value is sufficient for the data model */ /* This common table will be required when permitted operations for groups of databases is discussed */ CREATE TABLE dbo.OperationParams ( OperationType sysname NOT NULL CONSTRAINT CK_OperationParams CHECK (OperationType IN ('DatabaseBackup','DatabaseIntegrityCheck','IndexOptimize')), ParamTag nvarchar(255) NOT NULL, CONSTRAINT PK_OperationParams PRIMARY KEY CLUSTERED (OperationType,ParamTag) ) GO CREATE PROCEDURE [dbo].[CreateOperationParamTable] @procname sysname AS BEGIN DECLARE @sqlstmt nvarchar(max) -- Build the column list first SET @sqlstmt = ( SELECT ',[' + STUFF(PARAMETER_NAME,1,1,'') + '] ' + DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN '' WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)' ELSE '(' + STR(CHARACTER_MAXIMUM_LENGTH) + ')' END + ' NULL' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_CATALOG = db_name() AND SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = @procname AND PARAMETER_NAME <> '@Databases' ORDER BY ORDINAL_POSITION FOR XML PATH('') ) -- Add the create table and constraints DECLARE @quotedtablename sysname = QUOTENAME(@procname + 'Params') SET @sqlstmt = N'IF OBJECT_ID(''dbo.' + @quotedtablename + ''') IS NOT NULL DROP TABLE dbo.' + @quotedtablename + ';' + CHAR(13) + CHAR(10) + N'CREATE TABLE dbo.' + @quotedtablename + ' (' + CHAR(13) + CHAR(10) + N'[ParamTag] nvarchar(255) NOT NULL CONSTRAINT ' + STUFF(@quotedtablename,2,0,'PK_') + N' PRIMARY KEY CLUSTERED,' + CHAR(13) + CHAR(10) + N'[OperationType] sysname NOT NULL CONSTRAINT ' + STUFF(@quotedtablename,2,0,'CK_') + N' CHECK ([OperationType] IN (' + QUOTENAME(@procname,CHAR(39)+CHAR(39)) + N')) CONSTRAINT ' + STUFF(@quotedtablename,2,0,'DF_') + N' DEFAULT (' + QUOTENAME(@procname,CHAR(39)+CHAR(39)) + N')' + CHAR(13) + CHAR(10) + @sqlstmt + CHAR(13) + CHAR(10) + N',CONSTRAINT ' + STUFF(@quotedtablename,2,0,'FK_') + N' FOREIGN KEY ([OperationType],[ParamTag]) REFERENCES dbo.OperationParams ([OperationType],[ParamTag]))' -- Run it PRINT 'Running:' + CHAR(13) + CHAR(10) + @sqlstmt EXEC sp_executesql @sqlstmt END
The procedure is then called as follows to create the Params tables for each of Ola’s three stored procedures:
EXEC [dbo].[CreateOperationParamTable] 'DatabaseBackup' EXEC [dbo].[CreateOperationParamTable] 'DatabaseIntegrityCheck' EXEC [dbo].[CreateOperationParamTable] 'IndexOptimize' /* Insert some param values */ INSERT INTO dbo.OperationParams VALUES ('DatabaseBackup','FullBackups') INSERT INTO dbo.DatabaseBackupParams (ParamTag,Directory,BackupType,[Compress],[LogToTable]) values ('FullBackups','c:\dbadmin\backup','FULL','Y','Y') INSERT INTO dbo.OperationParams VALUES ('DatabaseIntegrityCheck','CheckDBs') INSERT INTO dbo.DatabaseIntegrityCheckParams (ParamTag,CheckCommands,[LogToTable]) values ('CheckDBs','CHECKDB','Y') INSERT INTO dbo.OperationParams VALUES ('IndexOptimize','ReindexReorgs') INSERT INTO dbo.IndexOptimizeParams (ParamTag,FragmentationLow,[LogToTable]) values ('ReindexReorgs','INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE','Y') SELECT * FROM dbo.OperationParams SELECT * FROM dbo.DatabaseBackupParams SELECT * FROM dbo.DatabaseIntegrityCheckParams SELECT * FROM dbo.IndexOptimizeParams
This results in creation of the DatabaseBackupParams table, the DatabaseIntegrityCheckParams table and the IndexOptimizeParams table:
With the Databases parameter removed we now have parameter tagged rows that can be applied to one or more groups of databases. Ola’s solution allows the Databases parameter to accept keywords such as USER_DATABASES, SYSTEM_DATABASES or a comma separated list of database names. Having omitted the Databases column from the above three tables we know need these tables to allow us to define groups of databases and the permitted operations for the groups:
CREATE TABLE [dbo].[DatabaseGroupParent] ( [GroupName] [nvarchar](40) NOT NULL, CONSTRAINT [PK_DatabaseGroupParent] PRIMARY KEY CLUSTERED ([GroupName] ASC) ) CREATE TABLE [dbo].[DatabaseGroup] ( [GroupName] [nvarchar](40) NOT NULL CONSTRAINT [FK_DatabaseGroup] FOREIGN KEY REFERENCES [dbo].[DatabaseGroupParent] ([GroupName]), [DatabaseName] [sysname] NOT NULL, CONSTRAINT [PK_DatabaseGroup] PRIMARY KEY CLUSTERED ([GroupName] ASC,[DatabaseName] ASC) ) CREATE TABLE [dbo].[DatabaseGroupOperations] ( [GroupName] [nvarchar](40) NOT NULL CONSTRAINT [FK_DatabaseGroupOperations_1] FOREIGN KEY REFERENCES [dbo].[DatabaseGroupParent] ([GroupName]), [OperationType] [sysname] NOT NULL, [ParamTag] [nvarchar](255) NOT NULL, CONSTRAINT [PK_DatabaseGroupOperations] PRIMARY KEY CLUSTERED ([GroupName] ASC,[OperationType] ASC,[ParamTag] ASC), CONSTRAINT [FK_DatabaseGroupOperations_2] FOREIGN KEY ([OperationType], [ParamTag]) REFERENCES [dbo].[OperationParams] ([OperationType], [ParamTag]) ) GO
One issue with Ola’s solution is the default parameters for the IndexOptimize @FragmentationMedium and @FragmentationHigh parameters. If you don’t specify these parameters the values used are ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’ and ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’ respectively. This compares to @FragmentationLow where if you do not specify this parameter the value used is NULL which signifies that reindex/reorganise is not used. NULL specified for any of these parameters specifies that reindex/reorganise is not used. In this table driven solution NULL is translated to DEFAULT when the store procedures are called. This presents problems for @FragmentationMedium and @FragmentationHigh. It’s not a problem for @FragmentationLow as NULL is the DEFAULT value. The workaround for @FragmentationMedium and @FragmentationHigh is to use a user defined keyword – for example, NO_INDEX_MAINT. Rather than hardcoding this string in the procedures the solution stores the following table:
CREATE TABLE dbo.OperationParamsNullKeyword ( OperationType sysname NOT NULL, ParamName sysname NOT NULL, NullKeyword nvarchar(max) NOT NULL, CONSTRAINT PK_OperationParamsNullKeyword PRIMARY KEY CLUSTERED (OperationType,ParamName) ) GO INSERT INTO dbo.OperationParamsNullKeyword VALUES ('IndexOptimize','FragmentationMedium','NO_INDEX_MAINT'), ('IndexOptimize','FragmentationHigh','NO_INDEX_MAINT') GO
In effect, the processing stored procedure will translate these keywords to ‘NULL’ and not ‘DEFAULT’. Ola has example ‘C. Update statistics on all user databases’ on his Index and Statistics page. The IndexOptimizeParams table containing
FragmentationLow FragmentationMedium FragmentationHigh UpdateStatistics
NULL NO_INDEX_MAINT NO_INDEX_MAINT ALL
will be translated to
EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’
Now on to the wrapper stored procedure that for a specified operation (backup, integrity check or index optimize), a specified group name or specific database name(s) or one of Ola’s database keywords and for a specific operation parameter tag runs Ola’s actual stored procedure. Here’s the stored procedure that does that:
/* Helper stored procedure that creates a string such as ",@Directory='c:\backups',@BackupType='FULL',@Verify='Y'" depending on your actual values in the corresponding Params table */ CREATE PROCEDURE [dbo].[GetOperationParams] @Operation sysname, @OperationParamTag nvarchar(255), @OperationParams nvarchar(max) OUTPUT AS BEGIN DECLARE @sqlstmt nvarchar(max) SET @sqlstmt = STUFF( ( SELECT '+'',@'+COLUMN_NAME+'=''+COALESCE('+ CASE WHEN ParamName IS NULL THEN '' ELSE 'CASE WHEN ['+COLUMN_NAME+']='''+NullKeyword+''' THEN ''NULL'' ELSE NULL END,' END + CASE WHEN CHARACTER_SET_NAME IS NOT NULL THEN 'QUOTENAME(['+COLUMN_NAME+'],CHAR(39)+CHAR(39))' ELSE 'CAST(['+COLUMN_NAME+'] AS NVARCHAR(2000))' END +',''DEFAULT'')' FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN dbo.OperationParamsNullKeyword ON OperationType = @Operation AND ParamName = COLUMN_NAME WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = @Operation + N'Params' AND ORDINAL_POSITION > 2 FOR XML PATH('') ), 1,1,'') SET @sqlstmt = N'SELECT @OperationParams = ' + @sqlstmt + N' FROM dbo.' + QUOTENAME(@Operation + 'Params') + ' WHERE ParamTag = ' + QUOTENAME(@OperationParamTag,CHAR(39)+CHAR(39)) EXEC sp_executesql @sqlstmt,N'@OperationParams varchar(max) OUTPUT',@OperationParams = @OperationParams OUTPUT END GO /* The wrapper stored procedure that you would use in your SQL Agent TSQL job step */ CREATE PROCEDURE [dbo].[RunOperation] @Operation sysname, @DatabaseGroup nvarchar(40) = NULL, @DatabaseName sysname = NULL, @OperationParamTag nvarchar(255) AS BEGIN DECLARE @OperationParams nvarchar(max) = NULL EXEC dbo.GetOperationParams @Operation = @Operation,@OperationParamTag = @OperationParamTag,@OperationParams = @OperationParams OUTPUT DECLARE @Databases nvarchar(max) = NULL IF @DatabaseName IS NOT NULL SET @Databases = @DatabaseName ELSE SET @Databases = STUFF( ( SELECT ',' + dg.DatabaseName FROM dbo.DatabaseGroup dg WHERE dg.GroupName = @DatabaseGroup /* Check that the group operation is permitted */ AND EXISTS ( SELECT 1 FROM dbo.DatabaseGroupOperations dgo WHERE dgo.GroupName = @DatabaseGroup AND dgo.OperationType = @Operation AND dgo.ParamTag = @OperationParamTag ) ORDER BY dg.DatabaseName FOR XML PATH('') ), 1,1,'') SET @Databases = '@Databases=''' + @Databases + '''' DECLARE @sqlstmt nvarchar(max) SET @sqlstmt = 'EXEC dbo.' + QUOTENAME(@Operation) + ' ' + @Databases + @OperationParams PRINT 'Running: ' + @sqlstmt EXEC sp_executesql @sqlstmt END GO
An example wrapper call would be
/* Create a database group */ INSERT INTO dbo.DatabaseGroupParent VALUES ('GroupUserDBs1') INSERT INTO dbo.DatabaseGroup VALUES ('GroupUserDBs1','TestDB'), ('GroupUserDBs1','DBAdmin') /* Populate the permitted group operations */ INSERT INTO dbo.DatabaseGroupOperations VALUES ('GroupUserDBs1','DatabaseBackup','FullBackups') /* Now the actual call to backup the database group */ EXEC dbo.RunOperation @Operation = 'DatabaseBackup',@DatabaseGroup='GroupUserDBs1',@OperationParamTag = 'FullBackups'
And the results would be
Running: EXEC dbo.[DatabaseBackup] @Databases='DBAdmin,TestDB',@Directory='c:\dbadmin\backup',@BackupType='FULL',@Verify=DEFAULT,@CleanupTime=DEFAULT,@CleanupMode=DEFAULT,@Compress='Y',@CopyOnly=DEFAULT,@ChangeBackupType=DEFAULT,@BackupSoftware=DEFAULT,@CheckSum=DEFAULT,@BlockSize=DEFAULT,@BufferCount=DEFAULT,@MaxTransferSize=DEFAULT,@NumberOfFiles=DEFAULT,@CompressionLevel=DEFAULT,@Description=DEFAULT,@Threads=DEFAULT,@Throttle=DEFAULT,@Encrypt=DEFAULT,@EncryptionAlgorithm=DEFAULT,@ServerCertificate=DEFAULT,@ServerAsymmetricKey=DEFAULT,@EncryptionKey=DEFAULT,@ReadWriteFileGroups=DEFAULT,@OverrideBackupPreference=DEFAULT,@NoRecovery=DEFAULT,@URL=DEFAULT,@Credential=DEFAULT,@MirrorDirectory=DEFAULT,@MirrorCleanupTime=DEFAULT,@MirrorCleanupMode=DEFAULT,@AvailabilityGroups=DEFAULT,@Updateability=DEFAULT,@LogToTable='Y',@Execute=DEFAULT Date and time: 2016-11-12 21:32:44 Server: WIN2016 Version: 13.0.1601.5 Edition: Developer Edition (64-bit) Procedure: [DBAdmin].[dbo].[DatabaseBackup] Parameters: @Databases = 'DBAdmin,TestDB', @Directory = 'c:\dbadmin\backup', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @AvailabilityGroups = NULL, @Updateability = 'ALL', @LogToTable = 'Y', @Execute = 'Y' Source: https://ola.hallengren.com Date and time: 2016-11-12 21:32:44 Database: [DBAdmin] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: SIMPLE Encrypted: No Differential base LSN: 34000000250700126 Last log backup LSN: N/A Date and time: 2016-11-12 21:32:44 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'c:\dbadmin\backup\WIN2016\DBAdmin\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) Outcome: Succeeded Duration: 00:00:00 Date and time: 2016-11-12 21:32:44 Date and time: 2016-11-12 21:32:44 Command: BACKUP DATABASE [DBAdmin] TO DISK = N'c:\dbadmin\backup\WIN2016\DBAdmin\FULL\WIN2016_DBAdmin_FULL_20161112_213244.bak' WITH NO_CHECKSUM, COMPRESSION Processed 488 pages for database 'DBAdmin', file 'DBAdmin' on file 1. Processed 7 pages for database 'DBAdmin', file 'DBAdmin_log' on file 1. BACKUP DATABASE successfully processed 495 pages in 0.026 seconds (148.512 MB/sec). Outcome: Succeeded Duration: 00:00:00 Date and time: 2016-11-12 21:32:44 Date and time: 2016-11-12 21:32:44 Database: [TestDB] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: SIMPLE Encrypted: No Differential base LSN: 34000011863500037 Last log backup LSN: N/A Date and time: 2016-11-12 21:32:44 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'c:\dbadmin\backup\WIN2016\TestDB\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) Outcome: Succeeded Duration: 00:00:00 Date and time: 2016-11-12 21:32:44 Date and time: 2016-11-12 21:32:44 Command: BACKUP DATABASE [TestDB] TO DISK = N'c:\dbadmin\backup\WIN2016\TestDB\FULL\WIN2016_TestDB_FULL_20161112_213244.bak' WITH NO_CHECKSUM, COMPRESSION Processed 4424 pages for database 'TestDB', file 'TestDB' on file 1. Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1. BACKUP DATABASE successfully processed 4426 pages in 0.168 seconds (205.778 MB/sec). Outcome: Succeeded Duration: 00:00:00 Date and time: 2016-11-12 21:32:44 Date and time: 2016-11-12 21:32:44
The table driven approach allows you to easily report out which options your solution is using as well as allowing you to change these options without having to make any changes to the SQL Agent jobs themselves. You can apply these options to user defined groups of databases.