Verify if column exists

Question

Post reply

Verify if column exists

river1

SSC-Insane

Points: 24309
More actions
November 3, 2011 at 8:31 am

#390756

Hi,
I need to create an new column in a table
with this statement:
ALTER TABLE dbo.CONTRIBUINTES ADD
DT_CADASTRAMENTO datetime NULL
GO
ALTER TABLE dbo.CONTRIBUINTES ADD CONSTRAINT
DF_CONTRIBUINTES_DT_CADASTRAMENTO DEFAULT convert(varchar,getdate(),120) FOR DT_CADASTRAMENTO
GO
But, before create it, i need to know if the column is not already created.
If the column is already in the table, then it does not performe the operation.
Can someone help?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

Try something like this:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTRIBUINTES' AND COLUMN_NAME = 'DT_CADASTRAMENTO')

ALTER TABLE dbo.CONTRIBUINTES ADD

DT_CADASTRAMENTO datetime NULL

ALTER TABLE dbo.CONTRIBUINTES ADD CONSTRAINT

DF_CONTRIBUINTES_DT_CADASTRAMENTO DEFAULT convert(varchar,getdate(),120) FOR DT_CADASTRAMENTO

I've not tested it but it should get you going. INFORMATION_SCHEMA contains lots of useful information.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

river1 SSC-Insane Points: 24309 More actions · Answer 2

I am sorry. It's in SQL Server 2000 not in 2005...

My mistake (the post is in the rong place) 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 3

i usually use dynamic sql for the command, as if i'm adding a table that doesn't exist, the script will fail validation.

IF EXISTS(SELECT 1

FROM sys.objects objz

INNER JOIN sys.columns colz

ON objz.object_id = colz.object_id

WHERE SCHEMA_NAME(objz.schema_id) = 'dbo'

AND objz.name = 'CONTRIBUINTES'

AND colz.name = 'DT_CADASTRAMENTO')

exec ('ALTER TABLE dbo.CONTRIBUINTES

ADD DT_CADASTRAMENTO DATETIME NULL')

--edit--

2000 version

IF EXISTS(SELECT 1

FROM sysobjects objz

INNER JOIN syscolumns colz

ON objz.id = colz.id

WHERE USER_NAME(objz.uid) = 'dbo'

AND objz.name = 'CONTRIBUINTES'

AND colz.name = 'DT_CADASTRAMENTO')

exec ('ALTER TABLE dbo.CONTRIBUINTES

ADD DT_CADASTRAMENTO DATETIME NULL')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

river1 SSC-Insane Points: 24309 More actions · Answer 4

In SQL server 2000 i can do like this:

if not exists (select * from syscolumns where name ='dt_cadastramento')

begin

alter statement here

end

correct?

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 5

river1 (11/3/2011)
I am sorry. It's in SQL Server 2000 not in 2005...
My mistake (the post is in the rong place) 🙁

You should still be able to get at INFORMATION_SCHEMA though...

http://msdn.microsoft.com/en-us/library/aa933204(v=sql.80).aspx

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

river1 SSC-Insane Points: 24309 More actions · Answer 6

river1

SSC-Insane

Points: 24309

November 3, 2011 at 9:07 am

#1402699

Thank you very much guys