Tom Collins sticks to the defaults:
Do you have a sql query to check every sql server database column and identify if a default value is applied to the column?
Click through for a script which does just that. Tom’s query goes against system views and there’s a separate way to get those details from sys.default_constraints if you prefer to have a second option. If you’re on an older version of SQL Server where CONCAT_WS() doesn’t exist, concatenate it yourself.
SELECT
CONCAT_WS('.', QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)), QUOTENAME(OBJECT_NAME(c.object_id))) AS TableName,
c.name AS ColumnName,
dc.name AS DefaultConstraintName,
dc.definition AS DefaultConstraintDefinition
FROM sys.default_constraints dc
INNER JOIN sys.columns c
ON dc.parent_object_id = c.object_id
AND dc.parent_column_id = c.column_id;