Script to add check column exist before add

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)

Script to add check column exist before add

Author

Topic

mcsenow
Starting Member

3 Posts

Posted - 2005-06-05 : 00:52:32

Hi,

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 Visitor 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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-05 : 01:19:57

This should do it


if exists (
	select *
	from
		information_schema.columns
	where
		TABLE_SCHEMA 	= 'dbo'			and
		TABLE_NAME 	= 'ICPC_Counter'	and
		COLUMN_NAME	= 'Admin_Flag'
	)
	begin
	print 'Column ICPC_Counter.Admin_Flag exists'
	end
else
	begin
	print 'Column ICPC_Counter.Admin_Flag does not exist'
	end

CODO ERGO SUM

mcsenow
Starting Member

3 Posts

Posted - 2005-06-16 : 09:35:23

Thank you very much. I will give it a try.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-16 : 10:11:04

This will also do the same


if col_length('ICPC_Counter','Admin_Flag') is null
print 'Column ICPC_Counter.Admin_Flag does not exist'
else
print 'Column ICPC_Counter.Admin_Flag exists'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources