If exists question

Question

If exists question

thgamble1

Ten Centuries

Points: 1212
More actions
May 4, 2005 at 8:43 am

#66263

I'm having a hard time trying to figure out what best area to look for using a google search so figured I would try here as well.
I need the syntax to simply check if a column (or index) exists on a table.
I'm wanting to automate some import processes and don't want every alter statement firing off if any portion of the import has already been completed anyhow.
Thanks for your help.

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Not elegant, but works for a column...

if exists (select sc.name from syscolumns sc(nolock) inner join sysobjects so(nolock) on sc.id = so.id where so.name = 'TABLEA' and sc.name = 'COLB')

print 'YAY'

else

print 'NAY'

For an index

if exists (select * from dbo.sysindexes where name = N'INDEX' and id = object_id(N'[dbo].[TABLEA]'))

print 'Yep, there is an index'

else

print 'No Index of that name'

Shamless self promotion - read my blog http://sirsql.net

mark baekdal-145375 SSCrazy Points: 2200 More actions · Answer 2

without using system tables as the system tables will not be available in SQLServer 2005...

use Northwind

GO

set nocount on

declare @table_owner sysname, @table_name sysname, @column_or_index_name sysname

declare @result varchar(25),@param nvarchar(261)

--set the variables

select

@table_owner = 'dbo'

, @table_name = 'Categories'

, @column_or_index_name = 'CategoryName'

, @result = 'No index or column exists'

--first check for the existance of a column with the name

if exists(select * from INFORMATION_SCHEMA.[COLUMNS] where TABLE_SCHEMA=@table_owner and TABLE_NAME=@table_name and COLUMN_NAME=@column_or_index_name)

begin

set @result = 'Column exists'

end else begin

if object_id('tempdb..#results')is not null drop table #results

create table #results (index_name sysname,index_description varchar(210),index_keys nvarchar(2078))

set @param = '[' + @table_owner + '].[' + @table_name + ']'

insert into #results(index_name,index_description,index_keys)

exec sp_helpindex @objname = @param

if exists(select * from #results where index_name=@column_or_index_name)

set @result = 'Index exists'

end

select result = @result

regards,

Mark Baekdal

http://www.dbghost.com

http://www.innovartis.co.uk

+44 (0)208 241 1762

Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

Lowell SSC Guru Points: 323518 More actions · Answer 3

For an index

if exists (select * from dbo.sysindexes where name = N'INDEX' and id = object_id(N'[dbo].[TABLEA]'))

print 'Yep, there is an index'

else

print 'No Index of that name'

ok that's good for an index in general, but what if you wanted to test if an index exists on a table on colB, for example? how would you get the index in that case?

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!

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

Try this...

create table #results (index_name sysname, index_description varchar(200), index_keys varchar(300))

insert into #results exec sp_helpindex TABLE

if exists(select * from #results where index_keys like '%COLUMN%')

print 'Yep, theres an index'

else

print 'Nope, no index'

Shamless self promotion - read my blog http://sirsql.net

scking SSCrazy Points: 2598 More actions · Answer 5

The procedure below will add the ID column if it doesn't exist in the Mcrl table. You could easily substitute parameters for the table name and column name.

IF NOT EXISTS (SELECT * FROM sysobjects obj

INNER JOIN syscolumns sys ON obj.ID=sys.ID

WHERE obj.Name = 'Mcrl' and sys.Name='ID')

ALTER TABLE Mcrl

ADD ID int IDENTITY(1,1)

CONSTRAINT PK_Mcrl PRIMARY KEY