Sql Default Constraints Checks

Default Constraints.sql

--
-- Default Constraint name should match table and column name
--
select
    'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' DROP CONSTRAINT ' + quotename(dc.name) as DropStatement,
    'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' ADD CONSTRAINT ' + quotename('DF_' + t.name + '_' + c.name) + '  DEFAULT ' + dc.definition + ' FOR ' + quotename(c.name) as CreateStatement
from
    sys.default_constraints dc
    inner join sys.schemas s
    on dc.schema_id = s.schema_id
    inner join sys.tables t
    on dc.parent_object_id = t.object_id
    inner join sys.columns c
    on dc.parent_object_id = c.object_id
    and c.column_id = dc.parent_column_id
where
    quotename(dc.name) <> quotename('DF_' + t.name + '_' + c.name)

--
-- Audit Column Default Constraints should be correct
--
select
    'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' DROP CONSTRAINT ' + quotename(dc.name) as DropStatement,
    'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' ADD CONSTRAINT ' + quotename('DF_' + t.name + '_' + c.name) + '  DEFAULT ' + ed.Definition + ' FOR ' + quotename(c.name) as CreateStatement
from
    sys.schemas s
    inner join sys.tables t
    on s.schema_id = t.schema_id
    inner join sys.columns c
    on t.object_id = c.object_id
    left outer join (
        values 
        ('CreatedUserId','(suser_name())'),
        ('CreatedUserName','(suser_name())'),
        ('CreatedDateTime','(sysdatetimeoffset())'),
        ('UpdatedUserId','(suser_name())'),
        ('UpdatedUserName','(suser_name())'),
        ('UpdatedDateTime','(sysdatetimeoffset())')
    ) as ed(ColumnName, Definition)
    on c.name = ed.ColumnName
    left outer join sys.default_constraints dc
    on s.schema_id = dc.schema_id
    and t.object_id = dc.parent_object_id
    and c.column_id = dc.parent_column_id
where
    c.name in ('CreatedUserId', 'CreatedUserName', 'CreatedDateTime', 'UpdatedUserId', 'UpdatedUserName', 'UpdatedDateTime')
    and (dc.definition <> ed.Definition or dc.definition is null)