Summary

This document will help to identify all schemas from the underlying MSSQL database. This will even help to capture the DB schema of all tables and associated columns. This will help us to investigate the issue where some tables are not accessible due to the "Could not read fields for table" error and eventually the transaction fails in Confluence upgrade or other processes. 

Environment

Microsoft SQL Server 

Solution

The below  SQL query will help us to know the Schema name for tables and even for columns as well. 

select SCHEMA_NAME();

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT	* FROM INFORMATION_SCHEMA.COLUMNS;

Confluence only supports the dbo schema. As outlined in this KB article, we can follow the following steps for changing the schema from another owner ( e.g. db_owner) to dbo. 

• Roll back Confluence to a pre-upgrade state, confirm Confluence is running without issues in the pre-upgrade state, and then stop Confluence.
• Backup the deployment, also backup the Confluence database (since the schema will be altered), and then execute the following SQL:
  
  

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'db_owner' -- here is the existing schema name
set @newschema = 'dbo' -- here is the one that is supported

while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
 begin
 select @table = name from sys.tables
 where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)
 set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table
 exec(@sql)
 end