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.