Use sp_MSforeachtable for creating table backups dynamicaly

Question

I'm trying to use sp_MSforeachtable to create backup tables dynamically using this query.

exec sp_MSforeachtable
@command1='Print ''[?_bckp]''', 
@command2='select * into ''[?_bckp_2022]'''' from ?;'
@whereand=' and o.Name in (
''<table list>''
) 
and schema_name(schema_id) = ''dbo''' 

I'm getting an error for @whereand but I think it is a bogus error. I think the problem is in the command2 at [?_bckp_2022]. The table name is coming out of command1 is looking like [[dbo]].[table_name]]_bckp]. I think the sql command generated in command2 is causing the error at the execution stage. My questions are:

1. What am I doing wrong?
2. How can I remove schema out of the table-generated name in command2?

Thanks

Answer 1

There was a comma missing. Thank everyone.

Answer 2

As stated in the comments, it is much easier to do it using sys.tables.

Use string_agg to concatenate the dynamic command and exec() to execute it

declare @sql nvarchar(max);

select  @sql    = string_agg ('select * into ' + quotename(name + '_bckp_2002') + ' from ' + quotename(name), ';' + char(13))
from    sys.tables t

print   @sql
exec    (@sql)

You can filter the required tables easily in the WHERE clause