Explain Codes LogoExplain Codes Logo

How do I find a default constraint using INFORMATION_SCHEMA?

sql
constraint-tracing
sys-views
sql-server
Alex KataevbyAlex Kataev·Oct 27, 2024
TLDR

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.