The IN_DB_UTIL_SP_CHECK_PARAMETER stored procedure can be used to validate the existence of various types of database objects as well as check the values of various other parameters that are often used within the IN_DB_UTIL framework.
List of supported database objects that have an owner and require the format of OWNER.OBJECT_NAME (FQN)
- CONSTRAINT
- FUNCTION
- INDEX
- PROCEDURE
- SEQUENCE
- SYNONYM
- TABLE
- TRIGGER
- TYPE
List of supported database objects that do not require an owner and use the format of OBJECT_NAME
- FILEGROUP
- SCHEMA
List of supported IN_DB_UTIL framework parameters
- BATCH_SIZE
- DEBUG
- DEFER_FK_CHECK
- EVENT
- FILLFACTOR
- IDENTIFIER
- IS_SYNC_UPGRADE
- KEEP_SRC_TABLE
- MAX_DOP
- MAX_MINUTES
| Parameter | Description | |
|---|---|---|
| CHECK_TYPE | Type: | INPUT |
| Datatype: | NVARCHAR(30) | |
| Default Value: | NULL | |
| Description | Specifies the parameter type being checked. | |
| Options | See the list of supported parameters above. | |
| CHECK_VALUE | Type: | INPUT |
| Datatype: | NVARCHAR(257) | |
| Default Value: | NULL | |
| Description | Specifies the value of the parameter type being checked. | |
| Options | Certain parameters are restricted to specific values. | |
| CHECK_STATUS | Type: | OUTPUT |
| Datatype: | SMALLINT | |
| Default Value: | NULL | |
| Description | Returned status regarding the parameter check results. The returned status depends on the type of object that is checked. | |
| Options |
For database objects it returns the count of objects found 0 = Object does not exist 1 = Object exists -1 = Ambiguous Object Name (more than one) For parameter checks that are not database objects then 0 = Success (Valid parameter value) -1 = Error (Invalid parameter value) |
|
| CHECK_MESSAGE | Type: | OUTPUT |
| Datatype: | NVARCHAR(250) | |
| Default Value: | NULL | |
| Description | Returned message regarding the parameter check results. | |
Example
DECLARE @V_CHECK_TYPE NVARCHAR(30), -- Parameter Type @V_CHECK_VALUE NVARCHAR(257), -- Parameter Value @V_CHECK_STATUS SMALLINT, @V_CHECK_MESSAGE NVARCHAR(250); BEGIN EXEC IN_CHECK_PARAMETER @CHECK_TYPE = N'INDEX', @CHECK_VALUE = 'inuser.DOC_IDX2', @CHECK_STATUS = @V_CHECK_STATUS OUTPUT, @CHECK_MESSAGE = @V_CHECK_MESSAGE OUTPUT; PRINT N'CHECK_STATUS = '+CONVERT(VARCHAR,@V_CHECK_STATUS); PRINT @V_CHECK_MESSAGE; END; GO