code_junkie

How do I check if a column exists in SQL Server?

Nov 11th, 2011
89
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.52 KB | None | 0 0
  1. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  2. WHERE TABLE_NAME='tablename' AND COLUMN_NAME='columname' )
  3.  
  4. SELECT count(*) AS [Column Exists]
  5. FROM SYSOBJECTS
  6. INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
  7. WHERE
  8. SYSOBJECTS.NAME = 'myTable'
  9. AND SYSCOLUMNS.NAME = 'Myfield'
  10.  
  11. If col_length('table_name','column_name') is null
  12. select 0 as Present
  13. ELSE
  14. select 1 as Present
  15.  
  16. if COLUMNPROPERTY(object_id('table_name'), 'column_name', 'ColumnId') is null
  17. print 'doesn't exist'
  18. else
  19. print 'exists'
Add Comment
Please, Sign In to add comment