====== tSQLt table tests ======

Inspired by [[https://github.com/sanderstad/PStSQLtTestGenerator]], thanks Sander!
===== Column attributes =====

<code tsql>
CREATE VIEW tsqlt_test_utils.TableColumnAttributes
AS

SELECT
  OBJECT_SCHEMA_NAME(o.[object_id]) AS TableSchema
, o.[name] AS TableName
, c.[name] AS ColumnName
, t.[name] AS TypeName
, c.max_length
, c.[precision]
, c.[scale]
, c.is_nullable
, CASE WHEN LOWER(hc.[description]) LIKE '%case-sensitive%' THEN 1 END AS IsCaseSensitive
, CASE c.is_identity WHEN 1 THEN 1 END AS is_identity
, pkc.index_column_id AS PkOrdinal
, dc.[definition] AS DefaultExpression
, cc.[definition] AS ComputedExpression
, cc.is_persisted
FROM sys.objects o  
  INNER JOIN sys.columns c ON c.[object_id] = o.[object_id]
  INNER JOIN sys.types t ON t.user_type_id = c.user_type_id
  LEFT JOIN sys.fn_helpcollations() hc ON hc.[name] = c.collation_name
  LEFT JOIN sys.computed_columns cc 
    ON cc.[object_id] = c.[object_id]
    AND cc.column_id = c.column_id
  LEFT JOIN sys.default_constraints dc 
    ON dc.parent_object_id = c.[object_id]
    AND dc.parent_column_id = c.column_id
  LEFT JOIN sys.indexes pk 
    ON pk.[object_id] = o.[object_id]
    AND pk.is_primary_key = 1
  LEFT JOIN sys.index_columns pkc
    ON pkc.[object_id] = pk.[object_id]
    AND pkc.index_id = pk.index_id
    AND pkc.column_id = c.column_id
WHERE o.[type] = 'U'
</code>

===== Generate tests =====
<code tsql>
DECLARE @objectName NVARCHAR(500) = '[sprockit].[Event]';
 
DECLARE @testSchema NVARCHAR(128) = QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName)) 
  + '_' + OBJECT_NAME(OBJECT_ID(@objectName)));
DECLARE @columnNames NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX);

/*
SELECT *
FROM tsqlt_test_utils.TableColumnAttributes
WHERE TableSchema = OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName))
AND TableName = OBJECT_NAME(OBJECT_ID(@objectName));
--*/

-- assert column existence
SET @columnNames = N'';

SELECT 
  @columnNames += '
, (''' + REPLACE(ColumnName, '''', '''''') + ''')'
FROM tsqlt_test_utils.TableColumnAttributes
WHERE TableSchema = OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName))
AND TableName = OBJECT_NAME(OBJECT_ID(@objectName));

SET @sql = 'CREATE PROCEDURE ' + @testSchema 
  + '.[test Assert ' + OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName)) 
  + '.' + OBJECT_NAME(OBJECT_ID(@objectName)) 
  + ' has the correct columns]
AS

-- ARRANGE
SELECT [name]
INTO #expected
FROM sys.columns
WHERE 0 = 1;

INSERT INTO #expected (
  [name]
) VALUES 
  ' + SUBSTRING(@columnNames, 5, LEN(@columnNames)) + ';

-- ACT
SELECT [name]
INTO #actual
FROM sys.columns
WHERE [object_id] = OBJECT_ID('''+ @objectName + ''')

-- ASSERT
EXEC tSQLt.AssertEqualsTable 
  @Expected = ''#expected''
, @Actual = ''#actual'';'

PRINT @sql;

-- individual column tests
DECLARE @columnName NVARCHAR(128);

DECLARE _myCursor CURSOR LOCAL FOR
  SELECT ColumnName
  FROM tsqlt_test_utils.TableColumnAttributes
  WHERE TableSchema = OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName))
  AND TableName = OBJECT_NAME(OBJECT_ID(@objectName));
 
OPEN _myCursor 
FETCH NEXT FROM _myCursor INTO @columnName
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
SET @sql = N'';

SELECT @sql = 'CREATE PROCEDURE ' + @testSchema 
  + '.[test Validate ' + OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName)) 
  + '.' + OBJECT_NAME(OBJECT_ID(@objectName)) + ' ' + @columnName + ']
AS
 
-- ARRANGE
DECLARE @tableSchema SYSNAME = ''' + OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName)) + ''';
DECLARE @tableName SYSNAME = ''' + OBJECT_NAME(OBJECT_ID(@objectName)) + ''';
DECLARE @columnName SYSNAME = ''' + @columnName + ''';

SELECT *
INTO #expected
FROM tsqlt_test_utils.TableColumnAttributes
WHERE 0 = 1;

INSERT INTO #expected (
  [TableSchema]
, [TableName]
, [ColumnName]
, [TypeName]'
 + CASE WHEN max_length IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [max_length]' ELSE '' END
 + CASE WHEN [precision] IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [precision]' ELSE '' END
 + CASE WHEN [scale] IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [scale]' ELSE '' END
 + CASE WHEN is_nullable IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [is_nullable]' ELSE '' END
 + CASE WHEN IsCaseSensitive IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [IsCaseSensitive]' ELSE '' END
 + CASE WHEN is_identity IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [is_identity]' ELSE '' END
 + CASE WHEN PkOrdinal IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [PkOrdinal]' ELSE '' END
 + CASE WHEN DefaultExpression IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [DefaultExpression]' ELSE '' END
 + CASE WHEN ComputedExpression IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [ComputedExpression]' ELSE '' END
 + CASE WHEN is_persisted IS NOT NULL THEN CHAR(13) + CHAR(10) + ', [is_persisted]' ELSE '' END
 + '
) VALUES (
  @tableSchema
, @tableName
, @columnName
, ''' + TypeName + ''''
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(max_length AS NVARCHAR(10)), N'')
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST([precision] AS NVARCHAR(10)), N'')        
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST([scale] AS NVARCHAR(10)), N'')            
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(is_nullable AS NVARCHAR(10)), N'')        
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(IsCaseSensitive AS NVARCHAR(10)), N'')    
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(is_identity AS NVARCHAR(10)), N'')        
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(PkOrdinal AS NVARCHAR(10)), N'')          
 + COALESCE(CHAR(13) + CHAR(10) + ', ''' + REPLACE(DefaultExpression , '''', '''''') + '''', N'')
 + COALESCE(CHAR(13) + CHAR(10) + ', ''' + REPLACE(ComputedExpression, '''', '''''') + '''', N'')
 + COALESCE(CHAR(13) + CHAR(10) + ', ' + CAST(is_persisted AS NVARCHAR(10)), N'')       
+ '
);

-- ACT
SELECT *
INTO #actual
FROM tsqlt_test_utils.TableColumnAttributes
WHERE TableName = @tableName
AND ColumnName = @columnName;

-- ASSERT
EXEC tSQLt.AssertEqualsTable 
  @Expected = ''#expected''
, @Actual = ''#actual'';'
FROM tsqlt_test_utils.TableColumnAttributes
WHERE TableSchema = OBJECT_SCHEMA_NAME(OBJECT_ID(@objectName))
AND TableName = OBJECT_NAME(OBJECT_ID(@objectName))
AND ColumnName = @columnName;

PRINT @sql;

  FETCH NEXT FROM _myCursor INTO @columnName
 
END
 
CLOSE _myCursor 
DEALLOCATE _myCursor 

</code>


{{tag> tsqlt}}