How to find Schema name for Tables and Columns in Microsoft SQLServer
Platform Notice: Data Center Only - This article only applies to Atlassian products 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.
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.
1
2
3
4
5
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:
Below SQL is constructed using the schema name "db_owner" logged in the error (comments included for clarity)
1
2
3
4
5
6
7
8
9
10
11
12
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)
endWas this helpful?