如何判断用Query从SQLServer中查出的表的那个字段是主键?(50分)

在MS SQL Server中主键字段是存在一张系统表里的, 可以用sp_tables找一下.

另外, sp_pkeys tablename也能得到tablename的主键名

var
tablename,keyname:string;
with query1 do
begin
sql.clear;
sql.add('sp_pkeys'+ tablebame);
execsql;
keyname:=FieldByName('COLUMN_NAME').asstring;
end;

执行 keyname:=FieldByName('COLUMN_NAME').asstring; 报错。

查询结果只有KEY_SEQ字段，请各位大虾赐教！

有啊
TABLE_QUALIFIER,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,KEY_SEQ,PK_NAME

确实没有呀！我用 ExecSql一个字段也不返回，用Open只返回一个字段，
大虾能否详细指点一下。

以下代码摘自 TDBBackup 2.0
// BDEDBBackup.pas

{ ================================================================================= }
<font color=red>function TBDEDBBackup.GetIndexDesc(ATableName: string): TIdxDescArray;
var
hIdxList: hDbicur;
IndexDesc: IDXDesc;
begin
SetLength(Result, 0);
try
// 注意: 这里的 ATableName 必须是一个完整的 Owner.Table 格式的表名
// 否则对 M$SQL 数据库将取不到索引列表
Check(DbiOpenIndexList(hDatabase, PChar(ATableName), nil, hIdxList));

while DbiGetNextRecord(hIdxList, DbiNoLock, @IndexDesc, nil) <> DbiErr_Eof do
begin
SetLength(Result, Length(Result) + 1);
Result[High(Result)] := IndexDesc;
end;
finally
Check(DbiCloseCursor(hIdxList));
end;
end;</font>

{ ================================================================================= }
function TBDEDBBackup.DoGetSQLScript(ATableName: string): string;
var
FieldDescs: TFLDDescArray;
IndexDescs: TIDXDescArray;
VCheckDescs: TVCHKDescArray;
function GetFieldName(AFieldNumber: Integer): string; // 根据 FieldNumber 取字段名
var
I: Integer;
begin
Result := '';
for I := Low(FieldDescs) to High(FieldDescs) do
with FieldDescs do
if iFldNum = AFieldNumber then
begin
Result := szName;
Break;
end;
end;
function FieldIsRequired(AFieldNumber: Integer): Boolean; // 根据 FieldNumber 确定字段是否必须
var
I: Integer;
begin
Result := False;
for I := Low(VCheckDescs) to High(VCheckDescs) do
with VCheckDescs do
if (iFldNum = AFieldNumber) and bRequired then
begin
Result := True;
Break;
end;
end;
function GetTableScript: string;
var
I: Integer;
FieldName: string;
FieldType: string;
UnitCount: Integer;
begin
Result := 'Create Table ' + FixTableName(ATableName) + '(';

for I := Low(FieldDescs) to High(FieldDescs) do
with FieldDescs, DataTypeList do
begin
if SQLAutoWrap then Result := Result + #13;

FieldType := IntToStr(iFldType);
try
UnitCount := StrToInt(Values[FieldType + ' UnitCount']);
except
UnitCount := 0;
end;

FieldName := szName;
if Pos(' ', FieldName) > 0 then FieldName := '"' + FieldName + '"';
Result := Result + ' ' + FieldName + ' ' + Values[FieldType];

case UnitCount of
1: Result := Result + Format('(%d)', [iUnits1]);
2: Result := Result + Format('(%d, %d)', [iUnits1, iUnits2]);
end;

if FieldIsRequired(iFldNum) then
Result := Result + ' ' + Values[DriverName + '_NotNull']
else
Result := Result + ' ' + Values[DriverName + '_Null'];

if I <> High(FieldDescs) then
Result := Result + ',';
end;
if SQLAutoWrap then Result := Result + #13;
Result := Result + ')' + SQLSeparator + #13;
end;
<font color=red>function GetIndexScript: string;
var
I: Integer;
J: Integer;
IndexName: string;
Unique: string;
begin
Result := '';
for I := Low(IndexDescs) to High(IndexDescs) do
with IndexDescs do
begin
if Result <> '' then Result := Result + #13;

if szName <> '' then // 索引名
IndexName := szName
else
IndexName := 'Primary';

if bUnique then
Unique := 'Unique'
else
Unique := '';

Result := Result
+ Format('Create %s Index %s on %s (',
[Unique, IndexName, FixTableName(ATableName)]);

for J := 0 to iFldsInKey - 1 do // 取索引字段信息
begin
Result := Result + GetFieldName(aikeyFld[J]);

if J <> iFldsInKey - 1 then Result := Result + ', ';
end;
Result := Result + ')' + SQLSeparator + #13;
end;
end;</font>
begin
FieldDescs := GetFieldDesc(ATableName);
<font color=red>IndexDescs := GetIndexDesc(ATableName);</font>
VCheckDescs := GetVCheckDesc(ATableName);

if SQLIncludeIndexes then
Result := GetTableScript + #13 + GetIndexScript
else
Result := GetTableScript;
end;

例如tablename = user.m01
先
select object_id('user.m01')
返回TABLE ID
例如=2073058421

然后
select
TABLE_QUALIFIER = convert(varchar(32),db_name()),
TABLE_OWNER = convert(varchar(32),user_name(o.uid)),
TABLE_NAME = convert(varchar(32),o.name),
COLUMN_NAME = convert(varchar(32),c.name),
KEY_SEQ = convert(smallint,c1.colid),
PK_NAME = convert(varchar(32),i.name)
from
sysindexes i, syscolumns c, sysobjects o, syscolumns c1
where
o.id = 2073058421
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and c.name = index_col ('user.m01', i.indid, c1.colid)
and c1.colid <= i.keycnt
and c1.id = 2073058421
order by 1, 2, 3, 5
自己研究一下吧

接受答案了.