Professional Web Applications Themes

writing an upgrade script using conditional information - Microsoft SQL / MS SQL Server

Greetings. Can someone give me some pointers or a pointer to some pointers here? I need to write a script that will do some conditional stuff that I don't know if it is possible in tsql. It would do like the following: if table.column exists and table.column.length < 60 alter table set column length to 60 endif How do I do this sort of "if" checking.. is it possible? Thanks Jeff Kish...

  1. #1

    Default writing an upgrade script using conditional information

    Greetings.

    Can someone give me some pointers or a pointer to some pointers here?

    I need to write a script that will do some conditional stuff that I don't know if it is possible in tsql.

    It would do like the following:

    if table.column exists and table.column.length < 60
    alter table set column length to 60
    endif

    How do I do this sort of "if" checking.. is it possible?

    Thanks

    Jeff Kish
    Jeff Guest

  2. #2

    Default Re: writing an upgrade script using conditional information

    syscolumns and sysobjects are the tables to use to get the information.
    Take a look at the structure of both.

    SELECT dbo.syscolumns.name as columnName, dbo.sysobjects.name AS
    tblName
    FROM dbo.syscolumns INNER JOIN
    dbo.sysobjects ON dbo.syscolumns.id =
    dbo.sysobjects.id
    WHERE (dbo.syscolumns.name = 'columnName')
    AND (dbo.syscolumns.length < 60)

    Do not, I repeat, DO NOT try to update syscolumns and sysobjects directly.
    That's just asking for trouble.

    "Jeff Kish" <com> wrote in message
    news:com... 
    know if it is possible in tsql. 


    raydan Guest

  3. #3

    Default Re: writing an upgrade script using conditional information

    Hi Jeff,

    Here is the code that would accomplish what you are trying to do:

    ---Assumption: Column type is Varchar. If Column type is Char use Char in
    place of Varchar.

    If Exists (
    Select *
    From SysObjects so
    Join SysColumns sc
    On sc.id = so.id
    Where so.id = Object_Id('YourTableName')
    And sc.name = 'YourColumnName'
    And sc.Length < 60)
    Begin
    Alter Table YourTableName
    Alter Column YourColumnName VarChar(60)
    End


    --
    -Ram Thiru
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm


    "Jeff Kish" <com> wrote in message
    news:com... 
    know if it is possible in tsql. 


    Ram Guest

  4. #4

    Default Re: writing an upgrade script using conditional information

    On Wed, 20 Aug 2003 12:52:24 -0700, "Ram [MSFT]" <microsoft.com> wrote:
     
    Thanks much,

    Jeff Kish
    Jeff Guest

  5. #5

    Default Re: writing an upgrade script using conditional information

    On Wed, 20 Aug 2003 16:38:51 -0400, "raydan" <nospamcom> wrote:
     
    ><microsoft.com> wrote: [/ref]
    >in 
    >> Thanks much,
    >>
    >> Jeff Kish[/ref]
    >[/ref]
    Oh thanks to you too!
    Jeff Kish
    Jeff Guest

Similar Threads

  1. Writing VB macro script in xls
    By Mick in forum PERL Modules
    Replies: 2
    Last Post: June 2nd, 06:53 PM
  2. Writing Session Variable Information To a Table
    By Michelle in forum ASP.NET General
    Replies: 5
    Last Post: August 27th, 02:00 AM
  3. Help writing conditional blocks
    By estafford in forum ASP.NET General
    Replies: 2
    Last Post: August 19th, 06:58 PM
  4. Replies: 0
    Last Post: July 25th, 09:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139