Check for existing column of a table

• Min Chen-141523

  SSC Rookie

  Points: 26

  More actions

  April 30, 2004 at 1:46 pm

  #111072

  How can I check to see if a column exists in a table by using

  the MS system store procedures?  

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  April 30, 2004 at 2:37 pm

  #504889

  With many, sp_help, sp_columns, etc.

  You can also use a query like this:

  IF EXISTS (SELECT 1 FROM syscolumns where name = 'ColName' AND OBJECT_NAME(id) = 'TableName') PRINT 'Exists' ELSE PRINT 'no exists'

• Min Chen-141523

  SSC Rookie

  Points: 26

  More actions

  May 3, 2004 at 2:53 pm

  #505042

  Thanks for the reply. I have another question:

  If I use the following:

  EXEC sp_columns @table_name = 'X', @column_name = 'YY'

  How do I check if the result set returned is empty or not.

   

   

• bdohmen

  SSCrazy

  Points: 2996

  More actions

  May 3, 2004 at 3:05 pm

  #505043

  After the execution of the procedure, you can check @@ROWCOUNT

  EXEC sp_columns @table_name='X', @column_name='YY'

  IF @@ROWCOUNT>0

  BEGIN

  Insert code here

  END

   

  Brian