CREATE VIEW Metadata.CheckConstraints
AS
SELECT FQN,
s.NAME AS SchemaName,
t.NAME AS TableName,
CheckFQN,
c.NAME AS CheckConstraint,
b.ColumnCount AS ColCnt,
b.ColumnList,
c.DEFINITION AS DEFINITION,
c.is_disabled AS IsDisabled,
c.is_not_trusted AS IsNotTrusted,
c.is_system_named AS IsSystemNamed,
CreateScript,
DropScript,
c.create_date AS CreateDate,
c.modify_date AS ModifyDate,
t.OBJECT_ID AS ObjectId
FROM sys.schemas AS s(NOLOCK)
INNER JOIN sys.tables AS t(NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.check_constraints AS c(NOLOCK) ON t.OBJECT_ID = c.parent_object_id
CROSS APPLY (
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) AS FQN,
QUOTENAME(s.NAME) + '.' + QUOTENAME(c.NAME) AS CheckFQN
) AS d
CROSS APPLY (
SELECT COUNT(*) AS ColumnCount,
Util.dbo.StringConcat(DISTINCT cl.NAME, ', ') AS ColumnList
FROM sys.columns AS cl(NOLOCK)
WHERE cl.OBJECT_ID = t.OBJECT_ID
AND CHARINDEX('[' + NAME + ']', c.DEFINITION, 1) > 0
) AS b
CROSS APPLY (
SELECT 'IF OBJECT_ID(''' + CheckFQN + ''') IS NOT NULL ALTER TABLE ' + FQN + ' DROP CONSTRAINT ' + QUOTENAME(c.NAME) AS DropScript,
'ALTER TABLE ' + FQN + ' ' + CASE
WHEN is_disabled = 1
THEN 'WITH NOCHECK '
ELSE ''
END + 'ADD CONSTRAINT ' + QUOTENAME(c.NAME) + ' CHECK ' + DEFINITION + CASE
WHEN is_disabled = 1
THEN '
ALTER TABLE ' + FQN + ' NOCHECK CONSTRAINT ' + QUOTENAME(c.NAME)
ELSE ''
END AS CreateScript
) AS kk