Hi Lacak2,
I've sort of figured out a possible fix for the indexkeys stuff.
The UpdateIndexDefs procedure needs some changes:
with qry do
begin
ReadOnly := True;
if FServerType = Sybase then
begin
// Apparently indid 0 is the table name in Sybase
// http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.7/title.htm
SQL.Text:='select si.name as idx_name, ' +
'si.indid, ' +
'index_col(object_name(si.id),indid,1) as col_name, ' +
{ status: flag 0x2/2: unique index}
'(si.status & 2)/2 as IsUnique, ' +
{ status: 0x800/2048: primary key}
'(si.status & 2048)/2048 as IsPrimaryKey ' +
{ status2: flag 0x1/1: index supports foreign key
(si.status2 & 1) as foreignkey}
'from ' +
'sysindexes si ' +
'where ' +
{ indid 0: table
indid 255: text, image, text chain, or Java off-row structure (large object—or LOB—structure).}
'si.indid > 0 ' +
'and ' +
'si.indid < 255 ' +
'and ' +
{Tablename may have multiple dots etc so we have to normalize }
'si.id = object_id('''+TableName+''')'+
';';
writeln('debug sql: ');
writeln(sql.text);
writeln('Still get errors; incorrect syntax near '';'');
end
else
begin
//MS SQL Server
//TODO: we can use "execute dbo.sp_helpindex 'TableName'" when Open on execute will fully work
SQL.Text:='select i.name as idx_name, i.indid, c.name as col_name'+
',indexproperty(i.id, i.name, ''IsUnique'')'+
',objectproperty(o.id, ''IsPrimaryKey'') '+
'from sysindexes i '+
' join sysindexkeys k on i.id=k.id and i.indid=k.indid '+
' join syscolumns c on k.id=c.id and k.colid=c.colid '+
' left join sysobjects o on i.name=o.name and i.id=o.parent_obj '+
'where i.id=object_id('''+TableName+''')'+
' and used>0 '+
'order by k.indid, k.keyno';
end;
Open;
end;
... but there are still errors - I'd have to check if they occur in the actual select statement or the one above (which runs fine in a query tool)
If you want to split out the sybase stuff as you suggested, I can wait for that and incorporate my changes in that - might make it easier to keep it up to date.
Thanks