SQL Server DataType Testing

Question

Hi Guys,

I have column called code in source table which is of type varchar(40) and they changed it from varchar(40) to varchar(65). We created a ETL Package and which is creating 3 tables. In all 3 table's the column need's to be changed from varchar(40) to varchar(65).

Now I need to test this change. I know By looking into the table structure we can simply say that the column is changed from varchar(40) to varchar(65).

Is there any other way to Test this change?

Any Help is greatly appreciated !!!!

Answer 1

Did 'they' keep the original data anywhere? Arrange to test that the values are still the same. Or perhaps it is now in use, are there any values longer than 40 characters?

Answer 2

Try DESC TableName gives you enough and more of confirmation.

Answer 3

Get column information using INFORMATION_SCHEMA views:

SELECT  c.DATA_TYPE,
        c.CHARACTER_MAXIMUM_LENGTH
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_SCHEMA = 'dbo' --//@todo: put your schema name here
    AND c.TABLE_NAME  = 'MyTableName1'
    AND c.COLUMN_NAME = 'MyColumnName1'

See here for description of returned columns, where you mostly need DATA_TYPE and CHARACTER_MAXIMUM_LENGTH.

You can, of course, create 1 query for all 3 checks using:

SELECT  c.DATA_TYPE,
        c.CHARACTER_MAXIMUM_LENGTH
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_SCHEMA = 'dbo' --//@todo: put your schema name here
    AND(   (c.TABLE_NAME = 'MyTableName1' AND c.COLUMN_NAME = 'MyColumnName1')
        OR (c.TABLE_NAME = 'MyTableName2' AND c.COLUMN_NAME = 'MyColumnName2')
        OR (c.TABLE_NAME = 'MyTableName3' AND c.COLUMN_NAME = 'MyColumnName3')
        )