how to deploy a script

Hello,

What i'm looking for is a way to write a script which would change few data type fields in a specific table, in a specific database. Like i want to change it from char(4) to varchar(15). And then deploy this script, so that when he executes it, changes are reflected in his database. I may use query analyzer to excute i think. Plz also tell me how to write that script.(not sure if its called script).

Thank You.

Use the ALTER TABLE ALTER COLUMN command. Here is an example:

Code Snippet

ALTER TABLE MyTable ALTER COLUMN Col1 VarChar(100)

where MyTable is the table you're updating and Col1 is the column that you want to change its type.

I hope this answers your question.

Best regards,

Sami Samir

|||

if you don't need to alter your source database you can use this approach,

You can get all the table scripts from "SQL Server - Enterprise Manager" using Generate SQL Script.

After that you can find the Char(7) datatype and replace with varchar(15). (you can simply use any text editors Find & Replace)

|||

ya, i used following,

ALTER TABLE GRNs
ALTER COLUMN GRNNo VARCHAR(15) NOT NULL

i was just wondering if it can be conditional, like what's use of running it if its already varchar.

is their anything like if else?

|||

Try the following script:

Code Snippet

Declare @.Type as Int

Declare @.Length as Int

Select @.Type = System_Type_ID, @.Length = Max_Length

From sys.objects Inner Join sys.columns On sys.objects.Object_ID = sys.columns.Object_ID

Where sys.objects.Name = GRNs And sys.columns.Name = GRNNo

--Check that the column actually exists

If @.@.RowCount <> 0

Begin

--Check if it is already Varchar (ID is 167) and length is already 15

If @.Type <> 167 Or @.Length <> 15

Alter Table GRNs Alter Column GRNNo Varchar(15)

End

I hope this helps.

Best regards,

Sami Samir