1

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?

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.

  • Fantastic! Didn't know that existed. – Jeff.Clark May 10 '17 at 17:12
  • Is this known to be buggy? Worked fine the other day, but trying to do it on a SP now and it is not behaving. It is putting the check for object in there, but not double quoting as desired in a sp_executesql – Jeff.Clark May 11 '17 at 20:21
  • I don't think it's buggy but I may not have noticed that it seems to work differently depending on the object being scripted – Mister Magoo May 12 '17 at 0:28
  • Have you tried using the "Modify" option instead of "script as create"? That works for me on SPs with the "Check for object existence" checked. – Mister Magoo May 12 '17 at 8:40
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)

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.