Monday, January 14, 2008
Testing SQL Tables
Have you ever wanted to test a table in the database for the correct structure? Ever had someone make a column nullable that shouldn't have been? ever have someone make a column VARCHAR instead of NVARCHAR? INT instead of BIGINT? SMALLDATETIME instead of DATETIME? This is an easy way for bugs to creep in - when the code is written for one version of the database and then someone makes a change... So if you haven't been testing your tables (SQL should be TDD'd too), perhaps you should...
 
Here is a handy stored procedure you could use that describes a table in an easy-to-test way.
 
CREATE PROCEDURE describe (@table_name varchar(90))
AS
SELECT DISTINCT
  sc.column_id as ColumnNumber,
  cols.column_name as Name,
  cols.data_type as Type,
  ISNULL(cols.character_maximum_length, 0) as Length,
  cols.is_nullable as Nullable
FROM

  information_schema.columns cols

  INNER JOIN sys.columns sc ON

    cols.column_name = sc.name

    AND OBJECT_NAME(sc.object_id) = @table_name

ORDER BY sc.column_id
 
Just call it with the table name and it produces a nice format the tests can use to extract the info they need to check.
 
# Name         Type     Length Nullable
1 Id           int      0      NO
2 TypeId       int      0      NO
3 Name         nvarchar 50     NO
4 Description  nvarchar 1500   NO
5 CreateDate   datetime 0      NO
6 UpdateDate   datetime 0     YES

just employ a data reader to read the data in the test, and your unit tests can ensure that all of the tables have the right structure.
SQL | Tools | Unit Tests
Monday, January 14, 2008 8:15:53 AM (Pacific Standard Time, UTC-08:00)  #    Comments [0]  |  Trackback
Comments are closed.
© Copyright 2012, John E. Boal