Does the column exist

[Iain]

Hi,

I'm writing an SQL script to update the structure of a
database. One of the tasks I need to do is insert a new
field into a table, but before I try to insert I want to
check if the field has already been added to the table.

I've managed to do it by using sp_columns, storing its
results in a temporary table and then checking the temp
table for the field... but surely they must be a simpler
way???

Thank you for any help you can offer.
Iain

[Anith Sen]

You can use the meta-data function COL_LENGTH like:

IF COL_LENGTH('tbl', 'col') IS NOT NULL
PRINT 'Col exists in tbl'
ELSE
PRINT 'Does not exist'

Refer to SQL Server Books Online for more details on T-SQL meta-data
functions.

--
- Anith
( Please reply to newsgroups only )

[Amy]

USE <DBNAME>

IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <Your Table Name>
AND
COLUMN_NAME = <Your Column Name>
)
BEGIN
<YOUR CODE HERE>
END

HTH

"Iain" <iain_...@hotmail.com> wrote in message
news:061801c34a1c$3f48c820$a001...@phx.gbl...

[Vishal Parkar]

Try:

declare @col varchar(500)
declare @tab varchar(500)
select @col='orderid', @tab='orders'
if exists(select 1 from information_schema.columns
where column_name = @col
and table_name= @tab)
print 'column exists'
else
print 'column does not exists'

--
-Vishal

"Iain" <iain_...@hotmail.com> wrote in message
news:061801c34a1c$3f48c820$a001...@phx.gbl...

[Peter Cwik]

Well your close. I would use the If exists phrase to look
at syscolumns instead of the temp table. If you preview a
Generate SQL script you will see how MS does it.

>.
>

[Jacco Schalkwijk]

The catch-all variation:

IF NOT EXISTS(SELECT * FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
WHERE t.table_schema = '<owner name>' -- usually dbo
AND t.table_name = '<table name>'
AND t.table_type = 'base table'
AND c.column_name = '<column name>')

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

"Iain" <iain_...@hotmail.com> wrote in message
news:061801c34a1c$3f48c820$a001...@phx.gbl...