I am trying to find all the backup tables I have just created with another script.
declare @bkup_tbls varchar(20) = '''%_' + LTRIM(RTRIM(CONVERT(CHAR(8), GETDATE(), 112 ))) + '''' select @bkup_tbls --'%_20210703' -- (1 row(s) affected) select * from sys.objects where name like @bkup_tbls and type = 'U' --(0 row(s) affected)
I have verified the tables exist in the sys.objects
catalog view by the same value generated by the above declared variable.
select * from sys.objects where name like '%_20210703' and type = 'U' --(3 row(s) affected)
Am I missing anything? Any help would be much appreciated.
SQL Server version: Microsoft SQL Server 2016 (RTM)
Found related question, but that didn’t help either
Advertisement
Answer
If you print out the backup variable value, you will see that it has single quotes:
'%_20210703'
Those single quotes are part of the string, not delimiters. You don’t want them.
What you really want is:
declare @bkup_tbls varchar(20) = '%_' + LTRIM(RTRIM(CONVERT(CHAR(8), GETDATE(), 112 )))
which produces the string:
%_20210703
I suspect that you are confusing parameter substitution with dynamic SQL, which is why you think the delimiting single quotes are necessary.