SQL Server – Constraints – Recreating Constraint Checks

Background

Quick follow-up to our last post, we found some “Check Constraints” that were identified out as “Not Trusted“, as well.

We went ahead and scripted a Stored Procedure that can be used for re-creating “Check Constraints”.

Script

Script – Stored Procedure



use master
go

if object_id('[dbo].[sp_RecreateConstraintCheck]') is null
begin

	exec('create procedure [dbo].[sp_RecreateConstraintCheck] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[sp_RecreateConstraintCheck]
(
	  @parent sysname = null
	, @referenced sysname = null	
	, @currentIsDisabled bit = 0
	, @currentIsNotTrusted bit = 0
	, @currentIsNotForReplication bit = 1
	, @parentRowCountLessThan		bigint = 1E32
	, @outputFormat					tinyint = 1
)
as

	/*
		@outputFormat
			a) 1 - Grid
			b) 2 - Text
	*/
	set nocount on;

	declare @CHAR_NEWLINE  varchar(30)
	declare @CHAR_BATCHES  varchar(30)

	declare @tblCache TABLE
	(
		  [id]					int not null identity(1,1)
		, [constraint]			sysname
		, [parent]				sysname
		, [column]				sysname
		, [dropClause]			nvarchar(4000)
		, [createClause]		nvarchar(4000)
		, [enableClause]		nvarchar(4000)

		, [isDisabled]			bit
		, [isNotTrusted]		bit
		, [IsNotForReplication] bit

		, [rowCount]			bigint
	)

	set @CHAR_NEWLINE = char(13) + char(10);
	set @CHAR_BATCHES=  char(13) + char(10) + 'go';

	;with cteCC
	(
		  objectID
		, [constraint]
		, [parent]
		, [column]
	)
	as
	(
		select

			  [objectID]
				 = tblCC.object_id

			, [constraint]
				 = quoteName(tblCC.name)

			, [parentObject]
				= 
					quoteName(object_schema_name(tblCC.parent_object_id))
					+ '.'
					+ quoteName(object_name(tblCC.parent_object_id))

			, [column]
				= 
					col_Name(
								  tblCC.parent_object_id
								, tblCC.parent_column_id
							)

		from   sys.check_constraints tblCC

		
	)

	, [cteRowCount]
	(
		  [objectID]
		, [rowcount]
	)
	as
	(

		select
				  object_ID
				, [rows] = sum(rows)

		from    sys.partitions tblSP

		/*
           Only consider Heaps (0) or the Clustered Index (1)
           as DMV contains row for each index defined on the table
        */
		where   tblSP.index_id in (0, 1)

		group by object_ID

	)
	insert into @tblCache
	(
		  [constraint]	
		, [parent]		
		, [column]	

		, [dropClause]	
		, [createClause]
		, [enableClause]

		, [isDisabled]
		, [isNotTrusted]			
		, [IsNotForReplication]

		, [rowCount]
	)

	select 

			  [constraint]
				= tblCC.name

			, [parent]
				= cteCC.[parent]

			, [column]
				= cteCC.[column]


			, [dropClause]
				=
					  ' ALTER TABLE '
					+ cteCC.[parent]
					+ ' DROP CONSTRAINT '
					+ cteCC.[constraint]
					+ '; '



			, [createClause]
				=
						' ALTER TABLE '
							+ cteCC.[parent]
							+ ' WITH NOCHECK '
							+ ' ADD CONSTRAINT '
							+ cteCC.[constraint]
							+ ' CHECK '
							+ tblCC.[definition]
							+ '; '


			, [enableClause]
				= 
						' ALTER TABLE '
						+ cteCC.[parent]
						+ ' WITH CHECK CHECK '
						+ ' CONSTRAINT '
						+ cteCC.[constraint]
						+ '; '


			, [isDisabled]
				= tblCC.is_disabled

			, [isNotTrusted]			
				= tblCC.[is_not_trusted]			

			, [isNotForReplication]			
				= tblCC.[is_not_for_replication]			

			, [rowCountParent]
			   = cteRCP.[rowCount]


	FROM    sys.check_constraints tblCC

	inner join cteCC

			on tblCC.object_id = cteCC.objectID


	inner join cteRowCount cteRCP

			on tblCC.parent_object_id = cteRCP.objectID


	where tblCC.parent_object_id 
			= case
			 	  when (@parent is null) then tblCC.parent_object_id
				  else object_id(@parent) 	
			  end	


	AND  tblCC.is_not_trusted 
			= isNull
				(
					  @currentIsNotTrusted
					, tblCC.is_not_trusted
				)

	AND    tblCC.is_disabled 
				= isNull
					(
					    @currentIsDisabled
					  , tblCC.is_disabled 
					)

	AND    tblCC.is_not_for_replication 
			= isNull(
					    @currentIsNotForReplication
					  , tblCC.is_not_for_replication
					)


	and    cteRCP.[rowCount]
			 < case
					when @parentRowCountLessThan is null then 1E32
					when (@parentRowCountLessThan = 0) then 1E32
					else @parentRowCountLessThan
			   end


	order by
			  cteCC.[parent]
			, cteCC.[constraint]


	/*
		If Display as a Grid
	*/
	if (@outputFormat =1)
	begin

		select 
				  [constraint]	
				, [parent]		
				, [column]	
				, [dropClause]	
				, [createClause]
				, [enableClause]

				, [isDisabled]
				, [isNotTrusted]			
				, [isNotForReplication]			

				, [rowCount]

		from   @tblCache

	end
	/*
		If Display as a Text
	*/
	else if (@outputFormat =2)
	begin

		select 
				[--sql]
					=  [dropClause]
					+ @CHAR_NEWLINE
					+ [createClause]
					+ @CHAR_NEWLINE
					+ [enableClause]
					+ @CHAR_BATCHES

		from   @tblCache

	end


go


 exec sys.sp_MS_marksystemobject '[dbo].[sp_RecreateConstraintCheck]'
 go

Invoke



declare
	  @parent				sysname
	, @referenced				sysname
	, @currentIsDisabled			bit
	, @currentIsNotTrusted			bit
	, @currentIsNotForReplication	        bit
	, @parentRowCountLessThan		bigint
	, @outputFormat				tinyint		

set @currentIsDisabled  = 0
set @currentIsNotTrusted  = 1
set @currentIsNotForReplication = 1

--set @outputFormat = 1
set @outputFormat = 2

--set @parentRowCountLessThan	= 1E6

exec [dbo].[sp_RecreateConstraintCheck]
	  @parent = @parent
	, @referenced = @referenced
	, @currentIsDisabled = @currentIsDisabled
	, @currentIsNotTrusted = @currentIsNotTrusted
	, @currentIsNotForReplication = @currentIsNotForReplication
	, @parentRowCountLessThan	= @parentRowCountLessThan
	, @outputFormat	 = @outputFormat	


Output:


--sql
ALTER TABLE [dbo].[InstructorsStandbyList] DROP CONSTRAINT [CK_InstructorsStandbyList_1]; 

ALTER TABLE [dbo].[InstructorsStandbyList] WITH NOCHECK  ADD CONSTRAINT [CK_InstructorsStandbyList_1] CHECK ([dayPart] like 'day' OR [dayPart] like 'evening'); 

ALTER TABLE [dbo].[InstructorsStandbyList] WITH CHECK CHECK  CONSTRAINT [CK_InstructorsStandbyList_1]; 
go

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s