How to find Schema name for Tables and Columns in Microsoft SQLServer
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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.
This article does two things:
Read-only diagnostic: Lists schemas, tables, and columns in your MSSQL database so you can confirm which schema owns your Confluence tables.
Schema migration (destructive): Provides a SQL script to move all tables from a non-
dboschema todbo(the only schema Confluence supports).
Run the read-only diagnostic first to confirm the problem before running any migration SQL.
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 thedboschema. As outlined in thisKB 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:
Pre-flight checklist (before running the schema migration):
Stop Confluence and confirm no users are connected.
Take a full database backup using your standard MSSQL backup tooling.
Take a full Confluence home + install directory backup.
Confirm the
@oldschemavalue in the SQL matches the schema reported by the diagnostic query (do not assume it isdb_owner).Run the migration on a non-production restore of the database first if possible.
Below SQL is constructed using the schema name "db_owner" logged in the error (comments included for clarity)
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)
endVerify the migration worked:
Re-run the diagnostic query:
SELECT * FROM INFORMATION_SCHEMA.TABLES;Confirm all Confluence tables now show
dboasTABLE_SCHEMA.Confirm no tables remain under the previous schema.
Start Confluence and confirm it reaches the dashboard without "Could not read fields for table" errors.
If errors persist, restore from the backup taken in the pre-flight checklist and engage Atlassian Support.
Related articles
Supported Platforms | Confluence Data Center 10.2 | Atlassian Documentation (confirms supported database versions for your Confluence DC release)
Production Backup Strategy | Confluence Data Center 10.2 | Atlassian Documentation
Was this helpful?