December 5, 2007 at 2:27 pm
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 . . .
December 5, 2007 at 2:52 pm
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]
December 5, 2007 at 4:40 pm
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
Change is inevitable... Change for the better is not.
December 5, 2007 at 6:39 pm
I think this is what I was looking for, I'll give it a try--thanks for the feedback!
December 5, 2007 at 10:24 pm
Not a problem... let us know either way after you try it? Thanks...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2007 at 6:05 am
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?
December 6, 2007 at 6:53 am
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
December 6, 2007 at 11:21 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply