checking the type of a column

Hello,
I want to make an ALTER COLUMN to change a column's type (say VARCHAR to
TEXT), and I need to check first if the given column has not been changed
yet (the command will be in a script that will likely be runned more than
once and I don't want SQL Server to raise an error if the column to be
altered has already been altered).
So, I 've go table "Models" and I want to change the type of column
"Description" to TEXT (which is xtype 35). The following test ensures this
will only be done once:
if not exists (select * from dbo.syscolumns where id =
object_id(N'[dbo].[Models]')
and xtype = 35 and name = 'Description')
My question: is there a more elegant way of doing this ' (in terms of ease
of maintenance, performance, etc.) ? Thanks!!> My question: is there a more elegant way of doing this ' (in terms of ease
> of maintenance, performance, etc.) ? Thanks!!
Yes it is. Use information schema views instead accessing system tables
directly.
Example:
use northwind
go
create table t (colA varchar(50))
go
if exists(select * from information_schema.columns where table_schema =
'dbo' and table_name = 't' and column_name = 'colA' and data_type = 'varchar
')
alter table t alter column colA text
go
select
*
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't'
and column_name = 'colA'
go
drop table t
go
AMB
"Jeff Robichaud" wrote:

> Hello,
> I want to make an ALTER COLUMN to change a column's type (say VARCHAR to
> TEXT), and I need to check first if the given column has not been changed
> yet (the command will be in a script that will likely be runned more than
> once and I don't want SQL Server to raise an error if the column to be
> altered has already been altered).
> So, I 've go table "Models" and I want to change the type of column
> "Description" to TEXT (which is xtype 35). The following test ensures this
> will only be done once:
> if not exists (select * from dbo.syscolumns where id =
> object_id(N'[dbo].[Models]')
> and xtype = 35 and name = 'Description')
> My question: is there a more elegant way of doing this ' (in terms of eas
e
> of maintenance, performance, etc.) ? Thanks!!
>
>|||OK, this sounds cleaner. But I wonder why MS does not use this approach in
Query Analyzer you generate some script ? They use sysobjects directly...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2EC973B1-31A8-42F2-AC03-51AEC0EC4799@.microsoft.com...
> Yes it is. Use information schema views instead accessing system tables
> directly.
> Example:
> use northwind
> go
> create table t (colA varchar(50))
> go
>
> if exists(select * from information_schema.columns where table_schema =
> 'dbo' and table_name = 't' and column_name = 'colA' and data_type =
> 'varchar')
> alter table t alter column colA text
> go
> select
> *
> from
> information_schema.columns
> where
> table_schema = 'dbo'
> and table_name = 't'
> and column_name = 'colA'
> go
> drop table t
> go
>
> AMB
> "Jeff Robichaud" wrote:
>|||Jeff,
You will find an explanation if you see "Information Schema Views
" in BOL.
AMB
"Jeff Robichaud" wrote:

> OK, this sounds cleaner. But I wonder why MS does not use this approach in
> Query Analyzer you generate some script ? They use sysobjects directly...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:2EC973B1-31A8-42F2-AC03-51AEC0EC4799@.microsoft.com...
>
>