Press "Enter" to skip to content

Finding SQL Server Columns with Defaults

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, AS ColumnName, 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;