Check Column Exist in a Table

Question

How do I check if a column exists in a table using a SQL query? I'm using Access 2007.

codingbadger · Accepted Answer · 2011-01-06T15:13:52.380

3

You can use the Information_schema views:

If Not Exists (Select Column_Name
               From INFORMATION_SCHEMA.COLUMNS
               Where Table_Name = 'YourTable'
               And Column_Name = 'YourColumn')
begin

-- Column doesn't exist

end

In addition, you may want to restrict the where clause further by including the Database and/or schema.

If Not Exists (Select Column_Name
               From INFORMATION_SCHEMA.COLUMNS
               Where Table_Name = 'YourTable'
               And Column_Name = 'YourColumn'
               And Table_Catalog = 'YourDatabaseName'
               And Table_Schema = 'YourSchemaName')

begin

-- Column doesn't exist

end

edited Jan 06 '11 at 15:13

answered Jan 06 '11 at 08:17

codingbadger

42,678
13
95
110

I get the error "SQL instruction not valid. Expeted DELETE, INSERT, PROCEDURE, SELECT or UPDATE". Why? – Piero Alberto Apr 03 '15 at 08:31
There is no INFORMATION_SCHEMA in MS Access. This is not a valid answer. INFORMATION_SCHEMA exists in MS SQL Server. – Aris Aug 05 '19 at 20:25

score 2 · Answer 2 · edited May 23 '17 at 12:14

2

if Exists(select * from sys.columns where Name = N'columnName'  
            and Object_ID = Object_ID(N'tableName'))

begin

    -- Column Exists

end

"REFERENCE"

edited May 23 '17 at 12:14

Community

1
1

answered Jan 06 '11 at 08:18

VoodooChild

9,776
8
66
99

That would be for SQL Server only - not clear what database the OP is using... – marc_s Jan 06 '11 at 08:47

score 2 · Answer 3 · answered Jan 06 '11 at 11:14

2

IF NOT EXISTS (SELECT 1
FROM syscolumns sc
JOIN sysobjects so
ON sc.id = so.id
WHERE so.Name = 'TableName'
AND sc.Name = 'ColumnName')
BEGIN
--- do your stuff
END

answered Jan 06 '11 at 11:14

Anil Soman

2,443
7
40
64

Check Column Exist in a Table

3 Answers3