SQL Server 2008 – Find Default Constraints On a Table Using T-SQL

This script returns all default constraints on a table in a SQL Server 2008 database. It gives me the name and definition of the constraints on Person.Address table in Adventure Works database. You can substitute the table name with any other table to get the constraints for that table.

SELECT    [name],
        [definition]
FROM sys.default_constraints
WHERE parent_object_id =
    (SELECT OBJECT_ID FROM sys.tables
    WHERE name = 'Address')