Invalid object name when iterating over all tables

August 1, 2022

I am trying to iterate over all the tables with a given schema name and make a copy in the same db with another given schema.

This is the script I am using:

use DoctorWho
declare @sql_query as nvarchar(max)
select @sql_query = concat('insert into doctor_generated.' , table_name , ' select * from ' , table_name , ';')
FROM INFORMATION_SCHEMA.tables
WHERE table_schema LIKE 'dbo%';
exec (@sql_query);

However this throws an error:

Invalid object name ‘doctor_generated.tblEpisodeEnemy

Upon searching this error, I’ve refreshed the local cache & made sure I am using the correct db.

Is there anything I am missing?

>Solution :

I suspect what you actually want is something like this. Firstly use string aggregation for your dynamic statement; I assume you are on a fully supported version of SQL Server as you don’t state you aren’t. Next use QUOTENAME to properly quote your objects and avoid injection.

Then you can execute your dynamic statement:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT @SQL = STRING_AGG(N'SELECT * INTO doctor_generated.' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';',@CRLF)
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.[name] = N'dbo';

--PRINT @SQL;
EXEC sys.sp_executesql @SQL;