Re: Check Column Data Type
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
Re: Check Column Data Type
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.
Code:
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