Search code examples
sql-serversql-server-2016

Use sp_MSforeachtable for creating table backups dynamicaly

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

Solution

  • There was a comma missing. Thank everyone.