SET NOCOUNT ON

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataQualityUnitTest]') AND type in (N'U'))

DROP TABLE [dbo].[DataQualityUnitTest]

GO

CREATE TABLE [dbo].[DataQualityUnitTest]

(

[testType] [varchar](10) NOT NULL,

[rules] [xml] NOT NULL,

CONSTRAINT [PK_DataQualityUnitTest] PRIMARY KEY CLUSTERED ( [testType] ASC )

)

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateColumnDefaultDataQualityRules]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[GenerateColumnDefaultDataQualityRules]

GO

CREATE PROCEDURE [dbo].[GenerateColumnDefaultDataQualityRules]

AS

BEGIN

SET NOCOUNT ON

DECLARE @x xml

SET @x =

(

SELECT Tag

, Parent

, null AS [Defaults!1]

, [table] AS [Default!2!table]

, [column] AS [Default!2!column]

, [value] AS [Default!2!value]

FROM

( SELECT

1 AS Tag,

0 AS Parent,

null AS [table],

null AS [column],

null AS [value]

UNION ALL

select

2 AS Tag,

1 AS Parent,

i.TABLE_NAME AS [table],

i.COLUMN_NAME AS [column],

i.COLUMN_DEFAULT AS [value]

FROM

information_schema.columns i

WHERE

NOT i.COLUMN_DEFAULT IS NULL

)x

FOR XML EXPLICIT

)

SELECT @x

END

GO

IF NOT OBJECT_ID('tempdb..#t') IS NULL

DROP TABLE #t;

GO

CREATE TABLE #t ( result xml )

INSERT INTO #t EXEC GenerateColumnDefaultDataQualityRules

INSERT INTO [DataQualityUnitTest] SELECT 'Defaults', result FROM #t

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExecLiteral]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[ExecLiteral]

GO

CREATE PROCEDURE [dbo].[ExecLiteral]

@literal nvarchar(max)

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE #results ( resultText nvarchar(MAX) null )

CREATE TABLE #literal ( literalText nvarchar(max) not null )

INSERT INTO #literal EXEC sp_executesql @literal

DECLARE @sqlText nvarchar(max)

DECLARE cur CURSOR FORWARD_ONLY KEYSET FOR

SELECT [literalText] FROM #literal

OPEN cur

FETCH NEXT FROM cur INTO @sqlText

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #results EXEC sp_executesql @sqlText

FETCH NEXT FROM cur INTO @sqlText

END

CLOSE cur

DEALLOCATE cur

SELECT * FROM #results

DROP TABLE #literal

DROP TABLE #results

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestDefaultsAndValueExistance]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[TestDefaultsAndValueExistance]

GO

CREATE PROCEDURE [dbo].[TestDefaultsAndValueExistance]

AS

BEGIN

DECLARE @unitTest xml

SELECT @unitTest = rules FROM DataQualityUnitTest WHERE testType = 'Defaults'

SELECT Rules.c.value('@table','sysname') AS table_name

, Rules.c.value('@column','sysname') AS column_name

, Rules.c.value('@value','sysname') AS column_default

FROM @unitTest.nodes('/Defaults/Default') Rules(c)

EXCEPT

SELECT table_name, column_name, column_default

FROM INFORMATION_SCHEMA.COLUMNS

WHERE NOT COLUMN_DEFAULT IS NULL

EXEC ExecLiteral 'DECLARE @unitTest xml;

SELECT @unitTest = rules FROM DataQualityUnitTest WHERE testType = ''Defaults'';

SELECT ''IF EXISTS ( SELECT * FROM '' + Rules.c.value(''@table'',''sysname'') + ''

WHERE '' + Rules.c.value(''@column'',''sysname'') + '' IS NULL ) SELECT ''''''+ Rules.c.value(''@table'',''sysname'') +''.''+ Rules.c.value(''@column'',''sysname'') + '' contains NULL values''''''

FROM @unitTest.nodes(''/Defaults/Default'') Rules(c);'

END

GO

EXEC [dbo].[TestDefaultsAndValueExistance]