|
Home > Archive > MS SQL Server > October 2006 > how to check for column Existance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
how to check for column Existance
|
|
|
| Hi guys,
need your help. i've no knowledge of DB.
i gotta create a script, in that i need to check for a column in the
table, if the column doesnt exists then i need to create a column.
i tried this code in my script for checking the column:
go
if ((SELECT COALESCE(COL_LENGTH(
'Table_name','Column
_name'),0) )=0)
alter table Table_name add Column_name bit
go
but somehow the condition is not working when i run the whole script.
but if i run this line of code only, then it works fine.
can anyone plz tell me what is the problem here or any better solution
for checking columns.
i would appriciate your help.
Thanks,
Lucky
| |
|
| try this...
if not exists
(select 1 from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'YOUR TABLE'
and Column_Name = 'YOUR COLUMN' )
exec ('alter table Table_name add Column_name bit')
"lucky" <tushar.n.patel@gmail.com> wrote in message
news:1160987956.262333.167020@h48g2000cwc.googlegroups.com...
> Hi guys,
> need your help. i've no knowledge of DB.
>
> i gotta create a script, in that i need to check for a column in the
> table, if the column doesnt exists then i need to create a column.
>
> i tried this code in my script for checking the column:
>
> go
> if ((SELECT COALESCE(COL_LENGTH(
'Table_name','Column
_name'),0) )=0)
> alter table Table_name add Column_name bit
> go
>
> but somehow the condition is not working when i run the whole script.
> but if i run this line of code only, then it works fine.
>
> can anyone plz tell me what is the problem here or any better solution
> for checking columns.
>
> i would appriciate your help.
>
> Thanks,
>
> Lucky
>
| |
| Paul Ibison 2006-10-24, 6:38 pm |
|
declare @tablename varchar(1000)
declare @columnname varchar(1000)
set @tablename = 'Table_name'
set @columnname = 'Column_name'
if exists(select * from information_schema.columns
where table_name = @tablename and column_name = @columnname)
begin
alter table Table_name add Column_name bit
end
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
| |
| Paul Ibison 2006-10-24, 6:38 pm |
| Sorry - my script should say not exists....
Cheers,
Paul Ibison
|
|
|
|
|