Professional Web Applications Themes

Does the column exist - Microsoft SQL / MS SQL Server

Hi, I'm writing an SQL script to update the structure of a database. One of the tasks I need to do is insert a new field into a table, but before I try to insert I want to check if the field has already been added to the table. I've managed to do it by using sp_columns, storing its results in a temporary table and then checking the temp table for the field... but surely they must be a simpler way??? Thank you for any help you can offer. Iain...

  1. #1

    Default Does the column exist

    Hi,

    I'm writing an SQL script to update the structure of a
    database. One of the tasks I need to do is insert a new
    field into a table, but before I try to insert I want to
    check if the field has already been added to the table.

    I've managed to do it by using sp_columns, storing its
    results in a temporary table and then checking the temp
    table for the field... but surely they must be a simpler
    way???

    Thank you for any help you can offer.
    Iain
    Iain Guest

  2. #2

    Default Re: Does the column exist

    You can use the meta-data function COL_LENGTH like:

    IF COL_LENGTH('tbl', 'col') IS NOT NULL
    PRINT 'Col exists in tbl'
    ELSE
    PRINT 'Does not exist'

    Refer to SQL Server Books Online for more details on T-SQL meta-data
    functions.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Does the column exist

    Try:

    declare col varchar(500)
    declare tab varchar(500)
    select col='orderid', tab='orders'
    if exists(select 1 from information_schema.columns
    where column_name = col
    and table_name= tab)
    print 'column exists'
    else
    print 'column does not exists'

    --
    -Vishal

    "Iain" <com> wrote in message
    news:061801c34a1c$3f48c820$gbl... 


    Vishal Guest

  4. #4

    Default Does the column exist

    Well your close. I would use the If exists phrase to look
    at syscolumns instead of the temp table. If you preview a
    Generate SQL script you will see how MS does it. 
    Peter Guest

Similar Threads

  1. Column chart need column to exceed axis maximum value
    By zanedev in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: May 8th, 04:26 PM
  2. Bound Column or Template Column (w dAdapater?) in DataGrid
    By Ravichandran Mahalingam in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 9th, 03:30 PM
  3. Convert an MS Access Yes/No column to a checkbox column in C# datagrid
    By Gregory Rampton in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 6th, 04:09 PM
  4. Replies: 1
    Last Post: July 29th, 04:31 PM
  5. Replies: 0
    Last Post: July 7th, 08:32 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