Results 1 to 7 of 7

Thread: IF EXISTS Column (for MS SQL 2000)

  1. #1
    Join Date
    Jul 2004
    Posts
    106

    IF EXISTS Column (for MS SQL 2000)

    Hello

    How do you check if a column exist for MS SQL 2000 ?

    for a table :

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[myTable]
    GO

    but I dont find it for a column

    Thank you

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can query information_schema.columns.

  3. #3
    Join Date
    Jul 2004
    Posts
    106
    ? I dont understand the meaning of your answer
    I got this code on another forum :

    if exists ( select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='tablename'
    and COLUMN_NAME='columname' )
    drop table [dbo].[myTable]

    but I want to drop the column not the table ?

    then maybe :

    if exists ( select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='tablename'
    and COLUMN_NAME='columname' )
    drop COLUMN 'columname'

    thank you for helping

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You can use 'alter table' to drop column. By the way, you original question is 'How do you check if a column exist for MS SQL 2000'.

  5. #5
    Join Date
    Jul 2004
    Posts
    106
    oops ! yessorry !

    then is that correct to drop a column if this colum exists ?

    if exists ( select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='tablename'
    and COLUMN_NAME='columname' )
    drop COLUMN 'columname'

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    You have to use 'alter table table_name drop column column_name'.

  7. #7
    Join Date
    Jul 2004
    Posts
    106
    thank you rmiao

Posting Permissions

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