How do I find a default constraint using INFORMATION_SCHEMA?

sql

constraint-tracing

sys-views

sql-server

byAlex Kataev·Oct 27, 2024

To locate a column's default constraint, use the following SQL query to combine the system catalog views and sys tables:

-- E.T. phone home... but we dial the database servers here!
SELECT 
    t.name AS TableName,  -- Say my name, say my name!
    c.name AS ColumnName, -- Just another regular Joe
    d.name AS ConstraintName, -- Who's constraining who now?
    d.definition AS ConstraintDefinition -- Spreading constraining definitions, like spreading cheer
FROM 
    sys.default_constraints d -- This is not the constraint you're looking for
INNER JOIN 
    sys.columns c -- Joining the Fellowship of Columns
ON 
    d.parent_object_id = c.object_id  -- Parenting 101 for database objects
    AND d.parent_column_id = c.column_id -- It's a parental column thing, you wouldn't understand
INNER JOIN 
    sys.tables t -- Let's join another Fellowship, shall we?
ON 
    d.parent_object_id = t.object_id -- This is where I left my parents!
WHERE 
    c.name = 'YourColumnName'  -- Just like Romeo seeking Juliet
    AND t.name = 'YourTableName'; -- It's like finding Waldo, but easier!

Replace 'YourTableName' and 'YourColumnName' to find the constraint name and definition for that specific column. You know, information_schema might be part of the ISO standard, but it unfortunately doesn't include default value constraints.

Why to prefer sys catalog views?

In SQL Server, default constraints are one of the few things the INFORMATION_SCHEMA doesn't house. For these not-so-common guests, we need to knock the door of sys catalog views.

Naming constraints: A game of names

Trust me, you'll thank yourself later for using a consistent naming convention for constraints. Makes life simpler when you plan any schema changes.

Operations on constraints: Setting them free

Need to modify or drop a constraint? Knowing the name of the default constraint is crucial. And sadly, INFORMATION_SCHEMA doesn't offer this info on a silver platter. sys views, however, do.

Sys views: Out with the old, in with the new

If you're still using deprecated system views (sysobjects & syscolumns) from SQL Server 2005 era, it's high time to migrate. Choose the robust catalog views in sys for future compatibility & modern standards.

Working with old SQL Server versions

Running on MS SQL Server 2000? Unfortunately, sys views aren't available here. In this case, consider INFORMATION_SCHEMA or other alternatives, but remember, they come with their set of drawbacks.

What the future holds: Compatibility and standards

To ensure compatibility with the ever-evolving SQL Server, stick to the standard methods for searching constraints. sys is today's standard, secure for your queries with default constraints.

Efficient if not elusive: Constraint tracing

When tracking constraint-related issues, efficient querying is as essential as a map for treasure hunt. Do not lean on INFORMATION_SCHEMA for defaults. Bank on sys view, your compass pointing to the right answers.

explain-codes / Sql / How do I find a default constraint using INFORMATION_SCHEMA?

Linked

How to drop SQL default constraint without knowing its name?



List names of all tables in a SQL Server 2012 schema



How to find which views are using a certain table in SQL Server (2008)?



Sql Server 2008- Get table constraints



Sql Server - where is "sys.functions"?

