SDU Tools: List Constraints with System Names 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 of my least favourite things to see is the use of default system names for constraints. So we added a tool that can help to find when this has happened. It’s called ListConstraintsWithSystemNames.

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.

Naming Constraints

With SQL Server, providing your own name for a constraint is optional. But we always recommend doing it.

First, it makes comparing databases easier. If you’re looking for things that are different across databases, as is common with CI/CD based systems, you don’t want constraints that have the same purpose but have different names. Tooling often has options to ignore this, but why chance it?

Second, with SQL Server, changing things that are related to a constraint requires you to know the name of the constraint. For example, removing a column in SQL Server requires you to first remove any default constraint. But there’s no option to do that without first finding the name of the constraint. Having a somewhat randomly chosen name for the constraint makes it much harder to write scripts that will work across copies of a database that have been deployed separately.

Third, I like to see constraints with meaningful names. When you conflict with a constraint, I want the error to make it obvious what you’ve done wrong. Having an obvious name is a good way to do that. You don’t want an error message telling you that you’ve violated some constraint with a random name.

Other DB Engines

It’s worth noting that other DB engines don’t let you name constraints, or at least some of them.

A good example is in PostgreSQL where you can’t name a default constraint. But that doesn’t matter because operations like dropping a column will auotmatically also drop any default constraint on the column.

I really wish SQL Server worked that way as well.

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:

https://youtu.be/YA_6891BDds

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.ListConstraintsWithSystemNames
@DatabaseName sysname,
@SchemasToList nvarchar(max) = N'ALL',  -- N'ALL' for all
@TablesToList nvarchar(max) = N'ALL'    -- N'ALL' for all
AS
BEGIN

-- Function:      ListConstraintsWithSystemNames
-- Parameters:    @DatabaseName sysname         -> Database to process
-- @SchemasToList nvarchar(max)  -> 'ALL' or comma-delimited list of schemas to list
-- @TablesToList nvarchar(max)   -> 'ALL' or comma-delimited list of tables to list
-- Action:        List constraints that have system generated names (prefer appropriate assigned names)
-- Return:        Rowset of constraints
-- Refer to this video: https://youtu.be/YA_6891BDds
--
-- Test examples: 
/*

USE WideWorldImporters;
GO

ALTER TABLE Sales.InvoiceLines
ADD DEFAULT 0 FOR TaxRate;
GO

USE DATABASE_NAME_HERE;
GO

EXEC SDU_Tools.ListConstraintsWithSystemNames 
     @DatabaseName = N'WideWorldImporters',
     @SchemasToList = N'ALL', 
     @TablesToList = N'ALL'; 

USE WideWorldImporters;
GO

ALTER TABLE Sales.InvoiceLines
DROP CONSTRAINT DF__InvoiceLi__TaxRa__119F9925; -- use the returned name here instead
GO

*/
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    DECLARE @SQL nvarchar(max) = 
'WITH SystemNamedConstraints
AS
(
    SELECT s.[name] AS SchemaName,
           t.[name] AS TableName,
           CAST(NULL AS sysname) AS ColumnName,
           cc.[name] AS ConstraintName,
           cc.type_desc AS ConstraintType
    FROM ' + QUOTENAME(@DatabaseName) + N'.sys.check_constraints AS cc
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
    ON t.object_id = cc.parent_object_id
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
    ON s.schema_id = cc.schema_id
    WHERE cc.is_system_named = 1

    UNION ALL

    SELECT s.[name] AS SchemaName,
           t.[name] AS TableName,
           c.[name] AS ColumnName,
           dc.[name] AS ConstraintName,
           dc.type_desc AS ConstraintType
    FROM ' + QUOTENAME(@DatabaseName) + N'.sys.default_constraints AS dc
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
    ON t.object_id = dc.parent_object_id
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
    ON c.object_id = t.object_id 
    AND c.column_id = dc.parent_column_id
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
    ON s.schema_id = dc.schema_id
    WHERE dc.is_system_named = 1

    UNION ALL

    SELECT s.[name] AS SchemaName,
           t.[name] AS TableName,
           NULL AS ColumnName,
           dc.[name] AS ConstraintName,
           dc.type_desc AS ConstraintType
    FROM ' + QUOTENAME(@DatabaseName) + N'.sys.key_constraints AS dc
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
    ON t.object_id = dc.parent_object_id
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
    ON s.schema_id = dc.schema_id
    WHERE dc.is_system_named = 1

    UNION ALL

    SELECT s.[name] AS SchemaName,
           t.[name] AS TableName,
           NULL AS ColumnName,
           fk.[name] AS ConstraintName,
           fk.type_desc AS ConstraintType
    FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_keys AS fk
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
    ON t.object_id = fk.parent_object_id
    INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
    ON s.schema_id = fk.schema_id
    WHERE fk.is_system_named = 1
)
SELECT snc.SchemaName, snc.TableName, snc.ColumnName, snc.ConstraintName, snc.ConstraintType
FROM SystemNamedConstraints AS snc
WHERE 1 = 1 '
    + CASE WHEN @SchemasToList = N'ALL' 
           THEN N''
           ELSE N'    AND snc.SchemaName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @SchemasToList + ''', N'','', 1))'
      END + @CRLF 
    + CASE WHEN @TablesToList = N'ALL' 
           THEN N''
           ELSE N'    AND snc.TableName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @TablesToList + ''', N'','', 1))'
      END + @CRLF + N'
ORDER BY snc.SchemaName, snc.TableName, snc.ColumnName, snc.Constraintname;';
    EXEC (@SQL);
END;

2025-02-13