Retrieving Column Defaults with INFORMATION_SCHEMA.COLUMNS in SQL Server

Using INFORMATION_SCHEMA.COLUMNS and COLUMN_DEFAULT:

This method leverages the INFORMATION_SCHEMA.COLUMNS view, which contains details about each column in your database. The COLUMN_DEFAULT column within this view displays the default value assigned to a column, if any. Here's an example:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
  AND COLUMN_DEFAULT IS NOT NULL;

This query retrieves the table name, column name, and default value (if present) for all columns in the specified table (YourTableName). By checking for a non-null value in the COLUMN_DEFAULT column, you can identify columns with default constraints.

Referencing system tables (restricted use):

While not recommended for general use due to potential compatibility issues across different SQL Server versions, you can utilize system tables like sys.objects and sys.default_constraints to retrieve information about default constraints. However, be aware that these tables are subject to change in future versions, and relying on them might hinder code portability.

Here's an example using system tables (use with caution):

SELECT o.name AS ConstraintName,
       s.name AS SchemaName,
       t.name AS TableName,
       dc.definition AS DefaultValue
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.tables t ON o.parent_object_id = t.object_id
INNER JOIN sys.default_constraints dc ON o.object_id = dc.object_id
WHERE o.type = 'D'; -- Filter for default constraints

This query retrieves the constraint name, schema name, table name, and default value definition for all default constraints in the database.