Thanks for the tip on "Check for object existence" I didn't know about that before. I settled on a single script for all CHECK constraints for TABLE/COLUMN rules with a generic stored proc that drops/creates each.
Here's the format of the main script:
DECLARE @l_cTableName NVARCHAR(100)
DECLARE @l_cConstName NVARCHAR(4000)
DECLARE @l_cConstExpr NVARCHAR(4000)
SET @l_cTableName = '[dbo].[csAtty]'
SET @l_cConstName = '[Hearing ID cannot be empty.]'
SET @l_cConstExpr = 'isnull([at_nhearing_id], 0)<>0'
EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr
SET @l_cConstName = '[Matter ID cannot be empty.]'
SET @l_cConstExpr = 'isnull([at_nmatter_id], 0)<>0'
EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr
Etc....
SET @l_cTableName = '[dbo].[csBlockTemplate]'
SET @l_cConstName = '[Description cannot be empty.]'
SET @l_cConstExpr = 'isnull(BT_MEVENT, '')<>'''''
EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr
Etc...
Then, I created a generic SP thusly:
-- Add the parameters for the stored procedure here
@p_cTableName VARCHAR(100) = '',
@p_cConstraintName VARCHAR(4000) = '',
@p_cConstraintExp VARCHAR(4000) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
EXEC ('ALTER TABLE ' + @p_cTableName + ' DROP CONSTRAINT ' + @p_cConstraintName)
END TRY
BEGIN CATCH
END CATCH
EXEC ('ALTER TABLE ' + @p_cTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @p_cConstraintName + ' CHECK (' + @p_cConstraintExp + ')')
EXEC ('ALTER TABLE ' + @p_cTableName + ' CHECK CONSTRAINT ' + @p_cConstraintName)
END
GO
This cuts down on repetitive code in my post deploy script and makes for better readability. I don't have any evidence to back this, but my gut tells me that using the TRY/CATCH rather than checking for existence may perform better...?
Thanks again.
Bill