code_junkie

Sql Server equivalent to Oracle's CREATE OR REPLACE VIEW

Nov 11th, 2011
96
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.26 KB | None | 0 0
  1. CREATE OR REPLACE VIEW MY_VIEW AS
  2. SELECT SOME_FIELD
  3. FROM SOME_TABLE
  4. WHERE SOME_CONDITIONS
  5.  
  6. if exists (select * from dbo.sysobjects
  7. where id = object_id(N'dbo.MyView') and
  8. OBJECTPROPERTY(id, N'IsView') = 1)
  9. drop view dbo.MyView
  10. go
  11. create view dbo.MyView [...]
  12.  
  13. if exists (select * from dbo.sysobjects
  14. where id = object_id(N'dbo.MyView') and
  15. OBJECTPROPERTY(id, N'IsView') = 1)
  16. drop view dbo.MyView
  17. go
  18. create view dbo.MyView [...]
  19.  
  20. IF OBJECT_ID('[dbo].[myView]') IS NOT NULL
  21. DROP VIEW [dbo].[myView]
  22. GO
  23. CREATE VIEW [dbo].[myView]
  24. AS
  25.  
  26. CREATE PROCEDURE dbo.DropView
  27. @ASchema VARCHAR(100),
  28. @AView VARCHAR(100)
  29. AS
  30. BEGIN
  31. DECLARE @sql VARCHAR(1000);
  32. IF OBJECT_ID('[' + @ASchema + '].[' + @AView + ']') IS NOT NULL
  33. BEGIN
  34. SET @sql = 'DROP VIEW ' + '[' + @ASchema + '].[' + @AView + '] ';
  35. EXEC(@sql);
  36. END
  37. END
  38.  
  39. EXEC dbo.DropView 'mySchema', 'myView'
  40. GO
  41. CREATE View myView
  42. ...
  43. GO
  44.  
  45. IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_myView]'))
  46. EXEC sp_executesql N'CREATE VIEW [dbo].[vw_myView] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]'
  47. GO
  48.  
  49. ALTER VIEW [dbo].[vw_myView]
  50. AS
  51. SELECT 'This is a code which should be replaced by the real code for your view' as [real_code]
  52. GO
Add Comment
Please, Sign In to add comment