Add a Column to Table

Question

Add a Column to Table

vimal_panchal

SSC Veteran

Points: 249
More actions
April 1, 2010 at 1:08 am

#225724

I want to add a column to a table in My SQL CE database by using the following command:
ALTER TABLE tblProducts ADD isDeleted bit
However I want to prevent the alter table command to be executed in case the column already exists.
I could not use IF Exist in SQL CE.
Anyone has an idea or solution for this?
Regards,
Vimal Panchal

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

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

Neal Sivley SSCommitted Points: 1766 More actions · Answer 1

Try this, Do a select count(*) from the system tables looking for the column name in the table you want, if the count > 0 then the column exist

DECLARE @Counter as INT

SELECT @Counter = COUNT(*)

FROM sysColumns

WHERE Blahhhhhh

IF @Counter > 0 THEN

BEGIN

Alter Table

END

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

vimal_panchal (4/1/2010)
I want to add a column to a table in My SQL CE database by using the following command:
ALTER TABLE tblProducts ADD isDeleted bit
However I want to prevent the alter table command to be executed in case the column already exists.
I could not use IF Exist in SQL CE.
Anyone has an idea or solution for this?
Regards,
Vimal Panchal

IF NOT EXISTS (SELECT *

FROM information_schema.columns

WHERE table_name = 'tblProducts'

AND column_name = 'isDeleted')

BEGIN

ALTER TABLE tblproducts ADD isDelete bit

END

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vimal_panchal SSC Veteran Points: 249 More actions · Answer 3

Thanks to both of you for your reply but i want to do this in SQL Compact Edition database..which is not supporting DECLARE keyword as well as i can not start my query with IF......

Thanks

Vimal

Ed B Hall of Fame Points: 3685 More actions · Answer 4

Can you use TRY CATCH?

BEGIN TRY

ALTER TABLE tblproducts ADD isDelete bit

END TRY

BEGIN CATCH

END CATCH

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

Check this...

http://msdn.microsoft.com/en-us/library/ms174123(SQL.90).aspx

And you can use if condition in your application code. Generate data set from the query to check the column using Information_Schema and take decission at application level code.

Stored Procedures are NOT supported in SQL Server CE.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Add a Column to Table

Cookies on SQLServerCentral