SDU Tools: List and Retrust Untrusted Check Constraints in SQL Server
Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. I do a lot of reviewing of database designs and one thing I always check for is check constraints that are not currently trusted. So we added a tool that can help to find these. It’s called ListUntrustedCheckConstraints.
The procedure takes three parameters.
@DatabaseName is the database to process @SchemasToList is a comma-delimited list of schema names to process @TablesToList is a comma-delimited list of table names to process
For the two list parameters, the value ALL can be supplied instead.
Untrusted Check Constraints
Check constraints are used to check the values in database columns. Often they will be used to constrain the values further than what the data type alone does. Perhaps there’s a code column that can only contain 1, 2, or 3. You can do that with a check constraint. But you can also implement much more complex logic.
What can happen though, is that the constraints are disabled for a period of time by using WITH NOCHECK, then they are re-enabled WITH CHECK. But the question is about what that means for the data that has been modified in the meantime.
Unless all the data was rechecked, SQL Server can’t depend on the values being valid.
Retrust Check Constraints
To make it easier to recheck/retrust check constraints, we’ve also added a second tool called RetrustCheckConstraints.
The procedure also takes three parameters.
@DatabaseName is the database to process @SchemasToList is a comma-delimited list of schema names to process @TablesToList is a comma-delimited list of table names to process
For the two list parameters, the value ALL can be supplied instead.
Find out more
You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:
You can use our tools as a set or as a great example of how to write functions like these.
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
http://sdutools.sqldownunder.com
Latest version of the code
Note: the code might wrap when displayed below. The Azure SQL DB version that only operates on the current database is simpler and can be seen by downloading the tools.
CREATE PROCEDURE SDU_Tools.ListUntrustedCheckConstraints
@DatabaseName sysname,
@SchemasToInclude nvarchar(max) = N'ALL', -- N'ALL' for all
@TablesToInclude nvarchar(max) = N'ALL' -- N'ALL' for all
AS
BEGIN
-- Function: ListUntrustedCheckConstraints
-- Parameters: @DatabaseName sysname -> Database to process
-- @SchemasToInclude nvarchar(max) -> 'ALL' or comma-delimited list of schemas to list
-- @TablesToInclude nvarchar(max) -> 'ALL' or comma-delimited list of tables to list
-- Action: Lists untrusted check constraints. Ignores disabled constraints.
-- Return: DatabaseName, SchemaName, TableName, CheckConstraintName
-- Refer to this video: https://youtu.be/d2-8ZMeaB0A
--
-- Test examples:
/*
USE WideWorldImporters;
GO
ALTER TABLE Sales.Invoices
NOCHECK CONSTRAINT CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON;
GO
ALTER TABLE Sales.Invoices
WITH NOCHECK CHECK CONSTRAINT CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON;
GO
USE DATABASE_NAME_HERE;
GO
EXEC SDU_Tools.ListUntrustedCheckConstraints
@DatabaseName = N'WideWorldImporters',
@SchemasToInclude = N'ALL',
@TablesToInclude = N'ALL';
GO
EXEC SDU_Tools.RetrustCheckConstraints
@DatabaseName = N'WideWorldImporters',
@SchemasToInclude = N'ALL',
@TablesToInclude = N'ALL';
GO
EXEC SDU_Tools.ListUntrustedCheckConstraints
@DatabaseName = N'WideWorldImporters',
@SchemasToInclude = N'ALL',
@TablesToInclude = N'ALL';
GO
*/
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @SchemaName sysname;
DECLARE @TableName sysname;
DECLARE @CheckConstraintName sysname;
DECLARE @SQL nvarchar(max) =
'WITH UntrustedCheckConstraints
AS
(
SELECT ss.[name] AS SourceSchemaName,
st.[name] AS SourceTableName,
cc.[name] AS CheckConstraintName
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.check_constraints AS cc
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS st
ON st.object_id = cc.parent_object_id
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS ss
ON ss.schema_id = st.schema_id
WHERE st.is_ms_shipped = 0
AND st.[name] <> N''sysdiagrams''
AND cc.is_not_trusted <> 0
AND cc.is_disabled = 0
)
SELECT N''' + @DatabaseName + N''' AS DatabaseName,
ucc.SourceSchemaName AS SchemaName,
ucc.SourceTableName AS TableName,
ucc.CheckConstraintName AS ContraintName
FROM UntrustedCheckConstraints AS ucc
WHERE 1 = 1 '
+ CASE WHEN @SchemasToInclude = N'ALL'
THEN N''
ELSE N' AND ucc.SourceSchemaName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @SchemasToInclude + ''', N'','', 1))'
END + @CRLF
+ CASE WHEN @TablesToInclude = N'ALL'
THEN N''
ELSE N' AND ucc.SourceTableName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @TablesToInclude + ''', N'','', 1))'
END + @CRLF + N'
ORDER BY ucc.SourceSchemaName, ucc.SourceTableName, ucc.CheckConstraintName;';
EXEC (@SQL);
END;
GO
CREATE PROCEDURE SDU_Tools.RetrustCheckConstraints
@DatabaseName sysname,
@SchemasToInclude nvarchar(max) = N'ALL', -- N'ALL' for all
@TablesToInclude nvarchar(max) = N'ALL' -- N'ALL' for all
AS
BEGIN
-- Function: RetrustCheckConstraints
-- Parameters: @DatabaseName sysname -> Database to process
-- @SchemasToInclude nvarchar(max) -> 'ALL' or comma-delimited list of schemas to list
-- @TablesToInclude nvarchar(max) -> 'ALL' or comma-delimited list of tables to list
-- Action: Tries to retrust untrusted check constraints. Ignores disabled constraints.
-- Return: Nil
-- Refer to this video: https://youtu.be/UM1NFlu4z28
--
-- Test examples:
/*
USE WideWorldImporters;
GO
ALTER TABLE Sales.Invoices
NOCHECK CONSTRAINT CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON;
GO
ALTER TABLE Sales.Invoices
WITH NOCHECK CHECK CONSTRAINT CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON;
GO
SELECT [name], is_disabled, is_not_trusted
FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = N'Invoices';
GO
USE DATABASE_NAME_HERE;
GO
EXEC SDU_Tools.RetrustCheckConstraints
@DatabaseName = N'WideWorldImporters',
@SchemasToInclude = N'ALL',
@TablesToInclude = N'ALL';
GO
USE WideWorldImporters;
GO
SELECT [name], is_disabled, is_not_trusted
FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = N'Invoices';
GO
*/
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @UntrustedCheckConstraints TABLE
(
UntrustedCheckConstraintID int IDENTITY(1,1) PRIMARY KEY,
SchemaName sysname,
TableName sysname,
CheckConstraintName sysname
);
DECLARE @Counter int;
DECLARE @SchemaName sysname;
DECLARE @TableName sysname;
DECLARE @CheckConstraintName sysname;
DECLARE @SQL nvarchar(max) =
'WITH UntrustedCheckConstraints
AS
(
SELECT ss.[name] AS SourceSchemaName,
st.[name] AS SourceTableName,
cc.[name] AS CheckConstraintName
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.check_constraints AS cc
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS st
ON st.object_id = cc.parent_object_id
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS ss
ON ss.schema_id = st.schema_id
WHERE st.is_ms_shipped = 0
AND st.[name] <> N''sysdiagrams''
AND cc.is_not_trusted <> 0
AND cc.is_disabled = 0
)
SELECT ucc.SourceSchemaName, ucc.SourceTableName, ucc.CheckConstraintName
FROM UntrustedCheckConstraints AS ucc
WHERE 1 = 1 '
+ CASE WHEN @SchemasToInclude = N'ALL'
THEN N''
ELSE N' AND ucc.SourceSchemaName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @SchemasToInclude + ''', N'','', 1))'
END + @CRLF
+ CASE WHEN @TablesToInclude = N'ALL'
THEN N''
ELSE N' AND ucc.SourceTableName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @TablesToInclude + ''', N'','', 1))'
END + @CRLF + N'
ORDER BY ucc.SourceSchemaName, ucc.SourceTableName, ucc.CheckConstraintName;';
INSERT @UntrustedCheckConstraints (SchemaName, TableName, CheckConstraintName)
EXEC (@SQL);
SET @Counter = 1;
WHILE @Counter <= (SELECT MAX(UntrustedCheckConstraintID) FROM @UntrustedCheckConstraints)
BEGIN
SELECT @SchemaName = SchemaName,
@TableName = TableName,
@CheckConstraintName = CheckConstraintName
FROM @UntrustedCheckConstraints
WHERE UntrustedCheckConstraintID = @Counter;
PRINT N'Attempting to trust ' + @SchemaName + N'.' + @TableName + N'.' + @CheckConstraintName;
SET @SQL = N'USE ' + QUOTENAME(@DatabaseName) + N'; '
+ N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
+ N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(@CheckConstraintName) + N';';
EXEC(@SQL);
SET @Counter = @Counter + 1;
END;
END;
GO
2025-02-25