Skip to content
This repository has been archived by the owner on Sep 7, 2021. It is now read-only.
This repository is currently being migrated. It's locked while the migration is in progress.

Invalid object name 'SYS.INDEXES' on SQL Server case sensitive collate #1414

Open
shaohua-deng opened this issue Sep 5, 2019 · 1 comment

Comments

@shaohua-deng
Copy link

shaohua-deng commented Sep 5, 2019

xorm: v0.7.6
SQL Server: 2017 docker on Ubuntu 18.04
The database is created with case sensitive collate like this:
CREATE DATABASE testdb COLLATE Chinese_PRC_CS_AS_WS;

The line below returns an error "Invalid object name 'SYS.INDEXES'"

rows, err := db.DB().Query(s, args...)

While it doesn't fail on another database created with a default collate SQL_Latin1_General_CP1_CI_AS
CREATE DATABASE testdb2

Then I copied the SQL statement to a database terminal and modified all the system table names to lower case, it succeed.

SELECT
    IXS.NAME                    AS  [INDEX_NAME],
    C.NAME                      AS  [COLUMN_NAME],
    IXS.is_unique AS [IS_UNIQUE]
FROM sys.indexes IXS
INNER JOIN sys.index_columns  IXCS
    ON IXS.OBJECT_ID=IXCS.OBJECT_ID  AND IXS.INDEX_ID = IXCS.INDEX_ID
INNER   JOIN  sys.columns C  
    ON IXS.OBJECT_ID=C.OBJECT_ID AND IXCS.COLUMN_ID=C.COLUMN_ID

As I see from Microsoft web site, all SQL server system objects are named in lower case
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017

@shaohua-deng
Copy link
Author

Here is my proposal of code change
shaohua-deng@d726b60

I only run the SQL statement directly, didn't have any go test.

If it's OK, I can submit a pull request.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

2 participants