A partial correction to the current script that is returning additional and unnecessary rows. This is in reference to another article from the same author.
Script adjusted to:
SELECT IDENTITY(INT, 1, 1) as Row_ID, CONVERT(VARCHAR(50), schema_name(t.schema_id)) as Schema_Name, CONVERT(VARCHAR(250), t.Name) as Table_Name, CONVERT(VARCHAR(120), c.Name) as Column_Name, CONVERT(VARCHAR(120), d.Name) as Default_Constraints_Name, CONVERT(VARCHAR(250), d.definition) as Default_Value
INTO #Defaults FROM sys.default_constraints d
LEFT JOIN sys.tables t ON d.parent_object_id = t.object_id
LEFT JOIN sys.columns c ON t.object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE (@TablesName = '' OR CONVERT(VARCHAR(250), t.Name) = @TablesName) AND (@ColumnName = '' OR CONVERT(VARCHAR(250), c.Name) = @ColumnName)
The current script also returned the following line. I did not bother adjusting the script since it was easier for me to simply clear that line. If you need this in an automated script, you need to handle such returns...
ALTER TABLE [dbo].[dtproperties] ADD CONSTRAINT [DF__dtpropert__versi__77FFC2B3] DEFAULT (0) FOR [version]