Create Partition Check Constraints

If you are partitioning a table by a low-cardinality column (a code, a group, a region), the cleanest way to keep the right rows in the right partitions is a CHECK constraint per value. This script finds candidate columns (by naming convention - columns containing 'Co' or 'Group'), scans their distinct values, and for any column with fewer than @DistinctValueThreshold distinct values it drops any existing constraint of the same name and recreates it as an IN (...) check constraint.

Tune the column-name filters and the exclusion list at the top before running it on your own schema.

SET NOCOUNT ON;
GO

--Columns with distinct counts greater then this will not have check constraints created.
DECLARE @DistinctValueThreshold INT
SET @DistinctValueThreshold = 100 

------------------------------------------------------------------------------------------
DECLARE @ConstraintName nVarChar(128)
DECLARE @SchemaName nVarChar(128)
DECLARE @ObjectName nVarChar(128)
DECLARE @ColumnName nVarChar(128)
DECLARE @DistinctValue nVarChar(MAX)
DECLARE @DistinctValues nVarChar(MAX)
DECLARE @tSQL nVarChar(MAX)

CREATE TABLE #DistinctValues
(
	Value nVarChar(2000)
)

DECLARE PrimaryKeys CURSOR FAST_FORWARD FOR
	SELECT DISTINCT
		schema_name(O.schema_id) AS SchemaName,
		O.name AS ObjectName,
		C.name AS ColumnName
	FROM
		sys.objects AS O
	INNER JOIN sys.indexes AS I
		ON I.object_id = O.object_id
	INNER JOIN sys.index_columns AS IC
		ON IC.object_id = I.object_id
		AND IC.index_id = I.index_id
	INNER JOIN sys.columns AS C
		ON C.object_id = IC.object_id
		AND C.column_id = IC.column_id
	WHERE
		O.is_ms_shipped = 0
		AND O.type = 'U'
		AND (
			C.name LIKE '%Co'
			OR C.name LIKE '%Group'
		)
		AND C.name NOT IN 
		(
			'StdMaintGroup'
		)
	ORDER BY
		schema_name(O.schema_id),
		O.name,
		C.name
OPEN PrimaryKeys

FETCH FROM PrimaryKeys INTO @SchemaName, @ObjectName, @ColumnName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	------------------------------------------------------------------------------------

	SET @ConstraintName = 'CK_Consol_' + @SchemaName + '_' + @ObjectName + '_' + @ColumnName

	IF @ConstraintName NOT IN ('CK_Consol_dbo_budoEMTH_Co')
	BEGIN--IF
		SET @DistinctValues = NULL

		SET @tSQL = 'INSERT INTO #DistinctValues(Value)'
			+ 'SELECT DISTINCT Convert(nVarChar(2000), [' + @ColumnName + ']) AS Value'
			+ ' FROM [' + @SchemaName + '].[' + @ObjectName + ']'
		EXEC(@tSQL)
			
		IF((SELECT COUNT(0) FROM #DistinctValues) < @DistinctValueThreshold)
		BEGIN--IF
			DECLARE PresentValues CURSOR FAST_FORWARD FOR
				SELECT Value FROM #DistinctValues ORDER BY Value
			OPEN PresentValues

			FETCH FROM PresentValues INTO @DistinctValue

			WHILE(@@FETCH_STATUS = 0)
			BEGIN--WHILE
				------------------------------------------------------------------------------------
				SET @DistinctValues = IsNull(@DistinctValues, '') + '''' + @DistinctValue + ''''
				------------------------------------------------------------------------------------
				FETCH FROM PresentValues INTO @DistinctValue
				
				IF(@@FETCH_STATUS = 0)
				BEGIN--IF
					SET @DistinctValues = @DistinctValues + ','
				END--IF
			END--WHILE

			CLOSE PresentValues DEALLOCATE PresentValues
		END--IF
		
		TRUNCATE TABLE #DistinctValues

		SET @tSQL = 'IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N''[' + @SchemaName + '].[' + @ConstraintName + ']'') AND parent_object_id = OBJECT_ID(N''[' + @SchemaName + '].[' + @ObjectName + ']''))'
			+ 'ALTER TABLE [' + @SchemaName + '].[' + @ObjectName + '] DROP CONSTRAINT [' + @ConstraintName + ']'
		EXEC(@tSQL)
		
		IF(@DistinctValues IS NOT NULL)
		BEGIN--IF
			PRINT 'Creating: ' + @ConstraintName + ' (' + @DistinctValues + ')'

			SET @tSQL = 'ALTER TABLE [' + @SchemaName + '].[' + @ObjectName + '] WITH CHECK ADD CONSTRAINT [' + @ConstraintName + '] CHECK ([' + @ColumnName + '] IN (' + @DistinctValues + '))'
			EXEC(@tSQL)
			--SET @tSQL = 'ALTER TABLE [' + @SchemaName + '].[' + @ObjectName + '] CHECK CONSTRAINT [' + @ConstraintName + ']'
			--EXEC(@tSQL)
		END--IF
	END--IF	
	------------------------------------------------------------------------------------
	FETCH FROM PrimaryKeys INTO @SchemaName, @ObjectName, @ColumnName
END--WHILE

CLOSE PrimaryKeys DEALLOCATE PrimaryKeys

DROP TABLE #DistinctValues