Check Column Data Type

[JPicasso]

We have a table that has a data type that needs to be changed.

We have a .sql script that checks if the table has been created, if not, creates it. If the table has been created, we use an ALTER TABLE .. ALTER COLUMN call to fix it. That's working.

The problem is that this script will be run on the client every day or so.
I don't know the implications of constantly running an "ALTER COLUMN" command needlessly
on a column that is already the correct data type.
So I'd like to not run the command if the column type is already fixed.
Unless someone can verify that the DB checks first, and really doesn't run anything if you ALTER COLUMN to the same type.

EDIT: SQL SERVER 2005

[techgnome]

What you'll need to do is 1) check to see if the column is there, and if it is 2) Check the column's data type, If the col isn't there, alter table and add it. If it is there, but the wrong type, then do the Alter Column

As for getting the data type..... I used to have a query that would pull that info... and it was for 2005 too..... If I can find it again, I'll post it.

-tg

[techgnome]

Ah-ha! ... Found it... sort of.... this is a stripped down version of the one I was thinking of but it has the info you are looking for.

select so.name as TableName,
    sc.name as ColumnName,
    case sc.xtype
        when 34 then 'image'
        when 35 then 'text'
        when 36 then 'uniqueidentifier'
        when 48 then 'tinyint'
        when 52 then 'smallint'
        when 56 then 'int'
        when 58 then 'smalldatetime'
        when 59 then 'real'
        when 60 then 'money'
        when 61 then 'datetime'
        when 62 then 'float'
        when 106 then 'decimal(' + convert(varchar(5), sc.xprec) + ',' + convert(varchar(5), sc.xscale) + ')'
        when 122 then 'smallmoney'
        when 167 then 'varchar(' + convert(varchar(5), sc.length) + ')'
        when 231 then 'nvarchar(' + convert(varchar(5), sc.length) + ')'
        when 104 then 'bit'
        when 175 then 'char(' + convert(varchar(5), sc.length) + ')'
        else '***unknown***'
    end as ColType,
    case sc.isnullable
        when 0 then 'NOT NULL'
        when 1 then 'NULL'
    end as Nullable,
    case
        when sc.colstat = 1 then 'IDENTITY(1,1)'
        else ''
    end as IsIdentity,
    sc.colid, sc.typestat,
    sc.colorder,
    sc.xtype, sc.xusertype,
    sc.length,
    sc.xprec, sc.xscale,
    sc.colstat, sc.status,
    sc.type, sc.usertype,
    sc.prec, sc.scale,
    sc.isnullable
from sysobjects so
inner join syscolumns sc
    on so.id = sc.id
inner join sysobjects pk
    on so.id = pk.parent_obj
        and pk.xtype = 'pk'
where so.xtype in ('U')
order by so.name, sc.colorder

All you should need to do is mark out the fields you don't need in the results, and add the table name to the where clause, and it should tell you what you want to know.

If it doesn't work, let me know.

-tg