Not rocket science, nor anything new or startling, but I seem to bump into this issue every now and then, and always have to go searching to remind myself how to fix it.
I was trying to update a database last night, and EF Core gave a really weird error about one of the tables not existing (which it did, and had pretty much since the database was first created).
After some frustrating investigation, I noticed that all the tables in the database had the database name as the schema, instead of dbo, which they usually have.
So, I needed to change the schema for these tables (and views and stored procedures), as well as change the default schema for the database user to ensure that any future objects were created with the right schema.
Changing the user schema was a single line of SQL…
alter user myDbUser with default_schema=dbo
The following SQL generated SQ that changed the schema for each table, view and stored procedure…
select 'alter schema dbo transfer [' + sysschemas.name + '].[' + dbobjects.name + '];'
from sys.objects dbobjects
inner join sys.schemas sysschemas on dbobjects.schema_id = sysschemas.schema_id
where sysschemas.name = 'oldSchemaName'
and (dbobjects.type in ('u', 'p', 'v'))
Running the output from this query changed all the table schema names and allowed the EF Core migrations to continue.
What I don’t understand is why it was all working up until now, but I guess I’ll never know that!
Be First to Comment