Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: If Exists Column ?

    Hello

    How do you check if a column exist ?

    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
    Jun 2003
    Posts
    269

    Thumbs up Re

    Code:
     
    if exists ( select * from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME='tablename' 
    and COLUMN_NAME='columname' )
    drop table [dbo].[myTable]
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    I want to drop the column not the table ?

    thank you for helping

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Here we go.........
    --alter table tablename drop column columnname
    Code:
    if exists ( select * from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME='tablename' 
    and COLUMN_NAME='columname' )
    alter table tablename drop  column columnname
    go
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is one way.....

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
    GO
    
    SELECT * FROM myTable99
    
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
    	ALTER TABLE myTable99 DROP COLUMN Col2
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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