Check Column Exist in a Table

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

---

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

---

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

begin

    -- Column Exists

end

“参考”

---

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

链接地址: http://www.djcxy.com/p/94416.html

上一篇: 检查OleDb表中是否存在列

下一篇: 检查表中是否存在列