CODEWITHSUNDEEP
sql-servermetadatatable-structure
user31071
user31071

Reputation: 271

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

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

Upvotes: 27

Views: 41518

Answers (6)

Gosai Drasti
Gosai Drasti

Reputation: 14

This query will work in SQL Server 2000

select * from sys.columns where name = 'ColumnName'

Upvotes: 0

Douglas Tondo
Douglas Tondo

Reputation: 219

I don't know if this script will work in sqlserver 2000, but in 2008 works:

SELECT COLUMNS.*
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS, INFORMATION_SCHEMA.TABLES TABLES
WHERE COLUMNS.TABLE_NAME=TABLES.TABLE_NAME AND UPPER(COLUMNS.COLUMN_NAME)=UPPER('column_name')

Upvotes: 0

Dan
Dan

Reputation: 21

This should do nicely:

if COLUMNPROPERTY(object_id('table_name'), 'column_name', 'ColumnId') is null
  print 'doesn\'t exist'
else
  print 'exists'

Upvotes: 2

user20358
user20358

Reputation: 14736

In query analyzer, select the Database that contains the table in which you need to check if the field exists or not and run the query below.

SELECT count(*) AS [Column Exists] 
FROM SYSOBJECTS  
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID 
WHERE 
 SYSOBJECTS.NAME = 'myTable' 
 AND SYSCOLUMNS.NAME = 'Myfield'

Upvotes: 5

Deepak Yadav
Deepak Yadav

Reputation: 1772

If col_length('table_name','column_name') is null
    select 0 as Present
ELSE
    select 1 as Present

Present will be 0, is there is no column_name present in table_name , otherwise 1

@CMS: I don't think that 'INFORMATION_SCHEMA.COLUMNS' have information about every table in DB. Because this didn't worked for me. But my answer did worked.

Upvotes: 10

Christian C. Salvadó
Christian C. Salvadó

Reputation: 827276

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME='tablename' AND COLUMN_NAME='columname' )

Upvotes: 39

Related Questions