CREATE VIEW Metadata.Defaults
AS
SELECT FQN,
s.NAME AS SchemaName,
t.NAME AS TableName,
c.NAME AS ColumnName,
ct.ColumnType AS ColumnDef,
d.NAME AS DefaultName,
d.DEFINITION AS [Definition],
d.is_system_named AS IsSystemNamed,
CreateScript,
DropScript,
d.create_date AS CreateDate,
d.modify_date AS ModifyDate,
t.OBJECT_ID AS ObjectId
FROM sys.schemas s(NOLOCK)
INNER JOIN sys.tables t(NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.default_constraints d(NOLOCK) ON D.parent_object_id = t.OBJECT_ID
CROSS APPLY (
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(T.NAME) AS FQN
) kk
INNER JOIN sys.columns c(NOLOCK) ON c.OBJECT_ID = T.OBJECT_ID
AND c.column_id = D.parent_column_id
INNER JOIN sys.types y(NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db(NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.NAME, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
CROSS APPLY (
SELECT 'IF OBJECT_ID(''' + QUOTENAME(s.NAME) + '.' + QUOTENAME(d.NAME) + ''') IS NOT NULL ALTER TABLE ' + FQN + ' DROP CONSTRAINT ' + QUOTENAME(d.NAME) AS DropScript,
'ALTER TABLE ' + FQN + ' ADD CONSTRAINT ' + QUOTENAME(d.NAME) + ' DEFAULT ' + DEFINITION + ' FOR ' + QUOTENAME(c.NAME) AS CreateScript
) k2
WHERE t.is_ms_shipped = 0