How to List all Default Constraints with Columns in SQL Server Database - SQL Server / TSQL Tutorial Part 92

Scenario:

You are working as SQL Server developer, you need to write script that should return all the Default Constraints with Column Names, Table Names and Schema name from a SQL Server Database.

Solution:

We can use system objects such as views and tables to get all the Default Constraints, Column Name, Table Names and Schema name from SQL Server database.

In below query we are using three system views to get required information.

 SELECT
    DB_Name() AS DBName,
    Schema_name(t.Schema_id)AS SchemaName, 
    t.name AS TableName, 
    c.name AS ColumnName, 
    d.name AS DefaultConstraintName, 
    d.definition AS DefaultDefinition
FROM sys.default_constraints d
INNER JOIN sys.columns c ON
    d.parent_object_id = c.object_id
    AND d.parent_column_id = c.column_id
INNER JOIN sys.tables t ON
    t.object_id = c.object_id

I executed above script on my database and it returned below results.

How to get Default Constraint Names with Columns from SQL Server Database

Video Demo : How to get list of all Default Constraints in SQL Server Database