mORMot Open Source

Quorren

Member

Registered: 2016-10-03

Posts: 14

MSSQL dublicate indexes.

Hi ab.
When i use mormot for storing data to external MSSQL database i have annoying message "Index already exists".

it is happened because column INDEX_COUNT always =0 (module SynDB function TSQLDBConnectionProperties.SQLGetField)
   

 dMSSQL,dMySQL, dPostgreSQL: FMT :=
    'select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,'+
    ' NUMERIC_SCALE, 0 INDEX_COUNT'+ // INDEX_COUNT=0 here (done via OleDB)
    ' from INFORMATION_SCHEMA.COLUMNS'+
    ' where UPPER(TABLE_SCHEMA) = ''%'' and UPPER(TABLE_NAME) = ''%''';

i suggest use my variant of query for MSSQL

    dMSSQL: FMT:=
      ' select  '+
      ' sys.all_columns.name as COLUMN_NAME,'+
      ' sys.types.name as DATA_TYPE,'+
      ' iif(is_ansi_padded=1,sys.all_columns.max_length,null) as CHARACTER_MAXIMUM_LENGTH, '+
      ' iif(is_ansi_padded=0,sys.all_columns.precision,null) as NUMERIC_PRECISION, '+
      ' iif(is_ansi_padded=0,sys.all_columns.scale,null) as NUMERIC_PRECISION,'+
      ' count(sys.index_columns.object_id) as INDEX_COUNT'+
      ' from '+
      ' sys.all_columns join sys.types on sys.all_columns.user_type_id=sys.types.user_type_id'+
      ' left join sys.index_columns on sys.all_columns.object_id = sys.index_columns.object_id and sys.all_columns.column_id = sys.index_columns.column_id'+
      ' where  sys.all_columns.object_id=object_id(''%.%'')'+
      ' group by '+
      ' sys.all_columns.name,'+
      ' sys.types.name,'+
      ' iif(is_ansi_padded=1,sys.all_columns.max_length,null), '+
      ' iif(is_ansi_padded=0,sys.all_columns.precision,null), '+
      ' iif(is_ansi_padded=0,sys.all_columns.scale,null)';

thank you

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: MSSQL dublicate indexes.

Will it work with most MSSQL versions available?

Quorren

Member

Registered: 2016-10-03

Posts: 14

Re: MSSQL dublicate indexes.

I checked and it works on:
2012
2014
2016

unfortunatelly last 2008 server i knew was upgraded to 2016 so i cant test 2008.

Don't know any 2005 server also.