Post reply

how can I tell if a colomn exists or not?

  • March 28, 2007 at 7:24 am

    #171877

    I'd like to use the following sql in a stored procedure, but I need to find out if the colomn exists or not, can anyone help?

    SET @sql = 'ALTER TABLE [programversion]  ADD [' + @col +'] varchar (200)'

    exec (@sql)

  • March 28, 2007 at 7:36 am

    #697387

    information_schema views are the most correct way to do it;

    IF EXISTS(select * from information_schema.columns where tablename='programversion' and columnname=@col )

    BEGIN

    SET @sql = 'ALTER TABLE [programversion]  ADD [' + @col +'] varchar (200)'

    exec (@sql)

    END

    this works too

    select * from syscolumns where object_id(id) = '' and name=''

    IF EXISTS(select * from syscolumns where object_id(id) = 'programversion' and name=@col )

    BEGIN

    SET @sql = 'ALTER TABLE [programversion]  ADD [' + @col +'] varchar (200)'

    exec (@sql)

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 28, 2007 at 7:40 am

    #697390

    Didn't you mean

    IF NOT EXISTS(select * from information_schema.columns where tablename='programversion' and columnname=@col )

    Far away is close at hand in the images of elsewhere.
    Anon.

  • March 28, 2007 at 7:40 am

    #697388

    many thanks Lowell, especially for the quick reply

  • March 28, 2007 at 7:44 am

    #697391

    That's true, but what's a 'NOT' between friends

  • March 28, 2007 at 7:45 am

    #697395

    Thanks David

  • March 28, 2007 at 7:55 am

    #697400

    I've just added your code to my Stored Procedure and when I do a Check syntax I get the following error message

    invalid colomn name 'tablename'

    invalid colomn name 'colomnname'

     

  • March 28, 2007 at 8:00 am

    #697405

    Mick

    Books Online tells you the names of the columns.  They're TABLE_NAME and COLUMN_NAME.

    John

  • March 28, 2007 at 8:00 am

    #697404

    that's what i get for rushing out an answer;

    David Burrows, as usual, is correct.

    NOT Exists...

    the fields are TABLE_NAME and COLUMN_NAMe...with underscores;

    select * from information_schema.columns where table_name='' and column_name=''

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 28, 2007 at 8:02 am

    #697407

    Thanks lads, it all works now thanks to you

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply