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.
