Create views only if database exists

[bigdavelamb]

Hi, I have a script that I execute, which checks whether a database exists, I wish to then only create the views if this DB exists. I have been attempting to use return, like this:

if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='velocity')
return

and i then have my create views after this, but it still executes the rest of the script, ( it seems to be due to the fact I have a GO commands after each view i then try to create, otherwise if I remove the GO's sql says 'CREATE VIEW' must be the first statement in a query batch. Anyone have any ideas?

Thanks,
Dave

 

[mrdenny]

Do the create view like this.

declare @SQLCMD varchar(8000)
if exists  FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='velocity')
set @SQLCMD = 'CREATE VIEW vw_Something as select * from db1.dbo.table2'
...

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bigdavelamb]

Hi, thanks for responding so quickly, just tried it and i still get 'CREATE VIEW' must be the first statement in a query batch.


dave

 

[mrdenny]

Can you post the code you are using. I may not be explaining my self clearly.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bigdavelamb]

Yeah Sure. Here is some of my original script.....

if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='velocity')
return

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'velocity', N'db chaining', N'false'
GO

use [velocity]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwAllTrans]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwAllTrans]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwAllTrans
AS
SELECT TOP 100 PERCENT dbo.Log_Transactions.LogID, dbo.Log_Transactions.ReaderType, dbo.Log_Transactions.UID1FirstName,
dbo.Log_Transactions.UID1LastName, dbo.Log_Transactions.UID1, CONVERT(char(8), dbo.Log_Transactions.PCDateTime, 112) AS FullDay,
dbo.Log_Transactions.PCDateTime, dbo.Log_Transactions.Description, dbo.Log_Transactions.FromZOne, dbo.Log_Transactions.ToZone,
dbo.UserCredentials.HostUserId
FROM dbo.Log_Transactions INNER JOIN
dbo.UserCredentials ON dbo.Log_Transactions.UID1 = dbo.UserCredentials.CredentialId
WHERE (dbo.Log_Transactions.FromZOne = 1) AND (dbo.Log_Transactions.Event = 2000) OR
(dbo.Log_Transactions.Event = 2000) AND (dbo.Log_Transactions.ToZone = 1)
ORDER BY dbo.Log_Transactions.UID1LastName, dbo.Log_Transactions.PCDateTime

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



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



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwFirstTime
AS
SELECT UID1, MIN(PCDateTime) AS FirstTime, CONVERT(char(8), PCDateTime, 112) AS YearDay, Event, FromZOne
FROM dbo.Log_Transactions
GROUP BY CONVERT(char(8), PCDateTime, 112), UID1, Event, FromZOne
HAVING (Event = 2000) AND (FromZOne = 1)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[mrdenny]

I meant the revised code using my recomendation.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com