Checking for a view or table

Question

Checking for a view or table

TaffyLewis

Ten Centuries

Points: 1033
More actions
December 5, 2007 at 2:27 pm

#177230

I'm looking for a way to check for a view or table and if it is there, do nothing, if it is not there create it. Typically there is the if then drop, this is not working for me:
[typical]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyTable]') AND type in (N'U'))
--typical drop view or drop table would go here. does anyone know a way to somehow do something like this
[what I would like to do]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyTable]') AND type in (N'U'))
Create view MyView etc . . .

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

Try this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YOUR_VIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[Your_VIEW]

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

drop table [dbo].[Your_Table]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I'm looking for a way to check for a view or table and if it is there, do nothing, if it is not there create it. Typically there is the if then drop, this is not working for me:

I think someone misread your intent....

Try this...

IF OBJECT_ID('table_name','U') IS NULL

BEGIN

...put table creation code here...

END

IF OBJECT_ID('view_name','V') IS NULL

BEGIN

...put view creation code here...

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TaffyLewis Ten Centuries Points: 1033 More actions · Answer 3

I think this is what I was looking for, I'll give it a try--thanks for the feedback!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Not a problem... let us know either way after you try it? Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TaffyLewis Ten Centuries Points: 1033 More actions · Answer 5

When I use the If OBJECT_ID() IS Null

I get the same snytax errors as with the other. I'm not sure why it is doing it but t-sql does not seem to like the create statement right after the If. This is what I'm doing:

IF OBJECT_ID('MyView','V') IS NULL

BEGIN

create view MyView as select * from MyTable

END

This generates a syntax errors just as the If Exists statement. Am I missing something?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

When creating or altering views, the statement CREATE VIEW should be the first one in the batch.

Its definitely not a good idea to create views in a procedure.

--Ramesh

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

Try like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TABLENAME]') AND type in (N'U'))

BEGIN

CREATE TABLE TABLENAME

(

COL1 INT,

COL2 VARCHAR(10)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VIEWNAME]') AND type in (N'V'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE VIEW [dbo].[VIEWNAME]

AS

SELECT *

FROM dbo.Test

'

END

GO

My blog: http://jahaines.blogspot.com