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;