Is it possible to have a script that double quotes a view or proc

Question

I apparently cannot figure out the key words to google search for what I want, so I need help finding out how to do this. I feel like this may be a duplicate, but I just cannot find this anywhere else.

I would like to be able to easily save a bunch of create table/view/proc scripts to a file. The file has an easy-to-execute format that will drop and then re-create all of the objects, so it can be run on multiple databases.

This format works well for me as I have to make changes to some objects during development, and then make the same changes on multiple other databases. I can just change the database and click execute.

However, some of the scripts have A LOT of quotes in them, and it gets QUITE annoying to have to double/quad quote them every time I make a change or add a new object to the file. For instance...

IF NOT EXISTS (SELECT * FROM sys.views WHERE NAME = 'MF1' AND schema_id = (SELECT schema_id FROM sys.schemas WHERE NAME = 'CFConversion'))
BEGIN

EXEC('CREATE VIEW [CFConversion].[MF1]
AS
...
...
...
CROSS APPLY (
VALUES (''T'', ''FIT', FIT, '''')
      ,(''T'', ''SIT', SIT, '''')
      ,(''T'', ''FUTA', FUTA, '''')
      ,(''T'', ''Employee_SUI'', EMPLOYEE_SUI, '''')
      ,(''T'', ''Employee_SDI'', EMPLOYEE_SDI, '''')
...
...
...

With tons of values in the Cross Apply, it just gets annoying. How can I easily export the definition of an object to have double/quad quotes?

Answer 1

In SSMS, you can check the option "Check for object existence" in Tools/Options/SQL Server Object Explorer/Scripting/Object Scripting options.

When you then script for CREATE from the Object Explorer, SSMS will script the object as an sp_executesql statement, fully double-quoted.

Answer 2

In case this helps anyone else, an alternative approach to the using double single-quotes to represent a single single-quote in a string is CHAR(39). That sentence is hard to follow, so as an example:

'Don''t'

can be represented using this approach as

'Don' + CHAR(39) + 't'

While this may not look as pretty with quick strings, it may clear up issues with large blocks of DSQL. To further clean it up, I also suggest you mask special characters with variables such as:

DECLARE @tick CHAR(1) = CHAR(39)
SELECT 'Don' + @tick + 't code with DSQL'

In my scripts that I want to keep clean, I often take this approach with Tabs CHAR(9) and newline (crlf) characters CHAR(13) + CHAR(10)

Answer 3

If you're pulling the data out in a query, then REPLACE(<value>, '''', '''''') should work quite nicely.

UPDATE: Edit correcting my mistake (have to double-up the single quotes in the double quotes) actually provided by @Steven Hibble.