Adding new column to an existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding new column to an existing table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-06 : 08:13:43

Fuji Nguyen writes "Hi,

My name is Fuji. I would like to know how to create a SQL script to
1. Check if a column is existed in a table
2. If column not existed, add a new column

Here is my situation. I developed and distributed a free DotNetNuke Vistor Counter module (http://corpdnn.icpconline.com). There was suggestion to enhance the module to add a column yes/no flag to allow setting of not increase the hit count if the visitor has admin role. I need to add a column call Admin_Flag with data type equal bit, default to 1. Part of the software upgrade, I can not drop the counter table and recreate new one. I know there is a script to check if table exist. Is there a similar script to check if column existed?

Below is the the counter table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ICPC_Counter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ICPC_Counter]
GO

CREATE TABLE [dbo].[ICPC_Counter] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleId] [int] NOT NULL ,
[ImageName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Digits] [int] NOT NULL ,
[HitCount] [bigint] NOT NULL ,
[IsPerSession] [bit] NOT NULL ,
[IsActive] [bit] NOT NULL
) ON [PRIMARY]
GO

Appreciate your helps"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-06 : 09:00:19

If col_Length('table','col') is null
--column does not exists Do your alter statement to add column
else
--Column exists

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-06 : 20:22:12

You shouldn't post the same question on two different threads:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50690

If you don't like the first answer you got, you should post on that thread to explain why it doesn't work for you.

CODO ERGO SUM

mcsenow
Starting Member

3 Posts

Posted - 2005-06-16 : 20:56:00

quote:
Originally posted by Michael Valentine Jones

You shouldn't post the same question on two different threads:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50690

If you don't like the first answer you got, you should post on that thread to explain why it doesn't work for you.

CODO ERGO SUM

Michael -
I am new to this forum. I did not know using the "Ask SQL Team" menu option is the same as posting a new thread. I thought this option will just email the question to the Team so I posted the same question as a thread under Transact SQL topic. I'll be careful next time. Appreciate your helps.

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-17 : 06:42:26

--Check for the existence of the column

if exists (select * from information_schema.columns where
table_name='Table_name' and column_name='column_name')

--If the above stmt is false the alter the table to add the column .

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources