Creating a viewscript

Creating a viewscript

In order to create or update a view:

• Design (or edit) the view in SSMS. Once you are satisfied with the view; create a viewscript by using the "DROP and CREATE" to clipboard command. (Right click on saved view in the object explorer, => Script View as => DROP And CREATE To => Clipboard)

• Create (or edit) your viewscript file in Visual Studio. All the view scripts are kept in the SQL project in the "ViewScripts" directory. Your view name (and filename) should start with "view". The file type is SQL. There is no view template, but the file should have some of the same header as any other SQL file (see Common/Support/ProgrammingRelated/FileTemplates/spTemplate.sql). Included fields should be:
  • Copyright
  • File
  • Name
  • Desc (description)
  • Auth (author)
  • Date
  • Change History

• Paste the clipboard code from SSMS into the section after the header comments.

• Delete the segments that set the database. (e.g.

  	USE [Lygaeoidea1]
  	GO

  ) There will be one before and after the code that drops the view if it exists.

• The code that sets ANSI_NULLS and QUOTED_IDENTIFIER should remain.
• The actual "CREATE VIEW" code must be modified. The tool to update database objects run from ApexTaxon does not handle a "CREATE VIEW" command properly. It must be modified into an "EXEC" command. Prepend "CREATE VIEW" with
  
  EXEC dbo.sp_executesql @statement = N'
  
  and add a trailing " ' " to the end of the command (just above the next "GO").

• The final part of the pasted script controls the display of the view diagram in the SSMS editor. It can be left or deleted as you prefer.

Your viewscript it now complete and can be returned to source control. You can run the script to update the view in the db or it will be updated the next time full builds are done and database objects are updated.