Re: checking the type of a column

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Jeff Robichaud (jfrobichaud_at_gmail.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 14:50:15 -0500

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...
>> 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
>> ease
>> of maintenance, performance, etc.) ? Thanks!!
>>
>>
>>

Relevant Pages

  • Re: ALTER TABLE: recreate columns
    ... change script" toolbar button. ... Enterprise Manager does not always do things efficiently - most of it ... >"Specifies whether the column can accept null values. ... >specified in ALTER COLUMN only if the column contains no null values. ...
    (microsoft.public.sqlserver.programming)
  • ALTER COLUMN to for INDENTIY?
    ... Is it possible to ALTER COLUMN to convert a column into an IDENTIY column, ... from a script? ... (I know you can do this from Enterprise Manager - that doesn't help) ...
    (microsoft.public.sqlserver.programming)
  • Re: ALTER IDENTITY setting...
    ... Enterprise manager will create a new table and drop the old one. ... it to generate the script to do this. ... > I just need to alter column with Identity. ... >> rename the new one). ...
    (microsoft.public.sqlserver.programming)
  • alter column
    ... I want to alter column that it's part of primary key. ... Before i run this script, ... The object 'PK_table1' is dependent on column 'column1'. ...
    (microsoft.public.sqlserver.programming)