30 October is a special holiday in my country.
I had to mark holiday in a certain table present
in any database.
I decided to use sp_msforeachdb
While trying to construct the expression , I found that I am unable
to surround a character with quote character.
I even tried using four quotes '''' but no success.
I gave up and used char(39) as a workaround.
here is the complete syntax
exec sp_MsForEachDb ' if exists( select null from [?].sys.tables where name = ''alldates'' ) begin print ''use '' + ''?'' +'' '' + '' update a set a.isholiday = '' + char(39) + ''Y'' + char(39) + '', a.holiday_name = '' + char(39) + ''Eid Milad'' + char(39) + '', a.gz_holiday ='' + char(39) + ''Y'' + char(39) + ''from alldates a where 1=1 and dt = '' + char(39) + ''2020/10/30'' + char(39) end '
the result will be
set a.isholiday = 'Y',
a.holiday_name = 'Eid Milad',
from alldates a
and dt = '2020/10/30'
Please note that If I replace char(39) with ''''
I don't get ' (quote) printed.
I must be missing some basics, please guide
how to print quote inside a print clause
Here is shcema of the table alldates used above.
CREATE TABLE [dbo].[alldates]( [dt] [date] NOT NULL, [isholiday] [varchar](1) NULL, [gz_holiday] [varchar](1) NULL, [holiday_name] [varchar](30) NULL, PRIMARY KEY CLUSTERED ( [dt] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]