Create view or SP, only if the DB contains a pattern
- by Randall Salas
Hi all:
I am working on a script, that needs to be run in many different SQL servers. Some of them, shared the same structure, in other words, they are identical, but the filegroups and the DB names are different. This is because is one per client.
Anyway, I would like when running a script, If I chose the wrong DB, it should not be executed. I am trying to mantain a clean DB. here is my example, which only works for dropping a view if exists, but does not work for creating a new one. I also wonder how it would be for creating a stored procedure.
Thx a lot.
if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[ContentModDate]') and OBJECTPROPERTY(id, N'IsView') = 1)
AND CHARINDEX('Content', DB_NAME()) 0
drop view [dbo].[ContentModDate]
GO
IF (CHARINDEX('Content', DB_NAME()) 0)BEGIN
CREATE VIEW [dbo].[Rx_ContentModDate] AS
SELECT 'Table1' AS TableName, MAX(ModDate) AS ModDate
FROM Tabl1 WHERE ModDate IS NOT NULL
UNION
SELECT 'Table2', MAX(ModDate) AS ModDate
FROM Table2 WHERE ModDate IS NOT NULL
END
END
GO
