How to validate the structure of a table?

29 views
Skip to first unread message

CSP

unread,
Jul 15, 2020, 3:08:55 AM7/15/20
to tSQLt
I want to validate the structure of a table and this simple test fails even though I expected it to pass. What needs to change?

EXEC tSQLt.NewTestClass 'MyTests';
GO

CREATE PROCEDURE [MyTests].[test Some test]
AS
BEGIN
    -- Assemble
    CREATE TABLE #Expected (id INT);

    -- Act

    -- Assert
    EXEC tSQLt.AssertEqualsTableSchema #Expected, 'dbo.MyTable';
END;
GO

CREATE TABLE dbo.MyTable (id INT);
GO

-- Result:
Test Procedure: [test].[MyTests].[test Some test] on ...
[MyTests].[test Some test] failed: (Failure) Unexpected/missing column(s)
|_m_|name|RANK(column_id)|system_type_id|user_type_id|max_length|precision|scale|collation_name|is_nullable|is_identity|
+---+----+---------------+--------------+------------+----------+---------+-----+--------------+-----------+-----------+
|>  |id  |1              |56[int]       |56[int]     |4         |10       |0    |!NULL!        |True       |!NULL!     |

Dmitrij Kultasev

unread,
Jul 15, 2020, 6:35:39 AM7/15/20
to ts...@googlegroups.com
Probably there is limitation with temp tables. You can create a normal table then it works.

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/2797e60f-4756-4016-9e5a-a15fc2acc027o%40googlegroups.com.

Blake, Larry

unread,
Jul 15, 2020, 8:14:41 AM7/15/20
to ts...@googlegroups.com

I haven’t tried this, but what if you compare the temp table to another temp table?  You can do this.

 

SELECT * FROM dbo.MyTable

INTO #Actual

 

tSQLt.AssertEqualsTableStructure #Expected, #Actual

CSP

unread,
Jul 16, 2020, 2:30:00 AM7/16/20
to tSQLt
Using two standard tables or using two temporary tables works well.

EXEC tSQLt.NewTestClass 'MyTests';
GO

CREATE PROCEDURE [MyTests].[test Some test]
AS
BEGIN
    -- Assemble
    CREATE TABLE #Expected (id INT);

    -- Act
    SELECT *
    INTO #Actual
    FROM dbo.MyTable

    -- Assert
    EXEC tSQLt.AssertEqualsTableSchema #Expected, #Actual;
END;
GO

CREATE TABLE dbo.MyTable (id INT);
GO

To unsubscribe from this group and stop receiving emails from it, send an email to ts...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ts...@googlegroups.com.

Reply all
Reply to author
Forward
0 new messages