Get all foreignkeys in use in a given table (MS SQL Server)

The system stored procedure “sp_fkeys ([tablename])” will only give a list of foreign key references to the given table.
The “GetForeignKeys” function above will give you the list of fields that are “foreign hold” from other tables.
Try out: You’ll need an AdoConnection to your DB …

unit Unit1;
 interface
 uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, DB, ADODB, StdCtrls, DBCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Memo1: TMemo;
    BStart: TButton;
    procedure BStartClick(Sender: TObject);
  private
    procedure GetForeignKeys(sTableName: string;
      MyConnection: TADOConnection;
      var SlForeignKeyName,
      SlKeysActTable,
      SlKeysForeignTable,
      SlForeignKeyTable: TStringList);
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

(* Get foreign keys *)
procedure TForm1.GetForeignKeys(sTableName: string;
  MyConnection: TADOConnection;
  var SlForeignKeyName,
  SlKeysActTable,
  SlKeysForeignTable,
  SlForeignKeyTable: TStringList);
var
  MySelectQuery, MyRecordQuery: TADOQuery;
  i: Integer;
  SlKeysAct, SlKeysFor: TStringList;
  sConstraintName, sForeignKeys, sForeignTable: string;

  (* Schlüssel ermitteln *)
  procedure GetKeys(sKeyList: string; fAct: Boolean);
  var
    i: Integer;
    sKey: string;
  begin
    i := 0;
    repeat
      Inc(i);
      if sKeyList[i] &lt;&gt; ',' then
      begin
        sKey := sKey + sKeyList[i];
      end
      else
      begin
        if fAct then
        begin
          SlKeysAct.Add(sKey);
        end
        else
        begin
          SlKeysFor.Add(sKey);
        end;
        if (Length(sKey) + 1) &lt; i then
        begin
          if sKey[(i + 1)] = ' ' then
          begin
            Inc(i);
          end;
        end;
        sKey := '';
      end;
    until (i = Length(sKeyList));
    if sKey &lt;&gt; '' then
    begin
      if fAct then
      begin
        SlKeysAct.Add(sKey);
      end
      else
      begin
        SlKeysFor.Add(sKey);
      end;
    end;
  end;

  procedure GetForeignKeyFieldsAndTable(sSQL: string);
  var
    i: Integer;
    sValue: string;
    iPos: Integer;
    fAddValue: Boolean;
    fInFields: Boolean;
  begin
    if Length(sSQL) &gt;= 10 then
    begin
      (* REFERENCES entfernen *)
      sValue := Copy(sSQL, 1, 10);
      if AnsiUpperCase(sValue) = 'REFERENCES' then
      begin
        Delete(sSQL, 1, 11);
      end;
      i         := 0;
      iPos      := 0;
      sValue    := '';
      fInFields := False;
      repeat
        Inc(i);
        fAddValue := False;
        (* "normal" lesen *)
        if (sSQL[i] &lt;&gt; '.') and
          (sSQL[i] &lt;&gt; ' ') and
          (sSQL[i] &lt;&gt; '(') and
          (sSQL[i] &lt;&gt; ')') and
          (fInFields = False) then
        begin
          sValue    := sValue + sSQL[i];
          fAddValue := True;
        end;
        (* In Felder *)
        if sSQL[i] = '(' then
        begin
          fInFields := True;
        end;
        if (fInFields) and (sSQL[i] &lt;&gt; '(') and (sSQL[i] &lt;&gt; ')') then
        begin
          sValue := sValue + sSQL[i];
        end;
        (* Felder verlassen *)
        if sSQL[i] = ')' then
        begin
          fInFields := False;
        end;
        if (fAddValue = False) and (fInFields = False) then
        begin
          case iPos of
            (* Datenbank *)
            0:
              begin
                sValue := '';
                Inc(iPos);
              end;
            (* Ower *)
            1:
              begin
                sValue := '';
                Inc(iPos);
              end;
            (* Tabelle *)
            2:
              begin
                sForeignTable := sValue;
                sValue        := '';
                Inc(iPos);
              end;
            (* Felder *)
            3:
              begin
                sForeignKeys := sValue;
                sValue       := '';
                Inc(iPos);
              end;
            else
              begin
              end;
          end;
        end;
      until (i = Length(sSQL));
    end;
  end;
begin
  try
    MySelectQuery := TADOQuery.Create(Application);
    with MySelectQuery do
    begin
      Name       := 'MyHelpSelectQuery';
      Connection := MyConnection;
      SQL.Add('sp_help ' + sTableName);
      Active := True;
    end;
    try
      MyRecordQuery := TADOQuery.Create(Application);
      with MySelectQuery do
      begin
        Name       := 'MyHelpRecordQuery';
        Connection := MyConnection;
        Recordset  := MySelectQuery.NextRecordset(i);
        Recordset  := MySelectQuery.NextRecordset(i);
        Recordset  := MySelectQuery.NextRecordset(i);
        Recordset  := MySelectQuery.NextRecordset(i);
        Recordset  := MySelectQuery.NextRecordset(i);
        if MySelectQuery.State = dsBrowse then
        begin
          Recordset := MySelectQuery.NextRecordset(i);
          if FindField('Constraint_Type') &lt;&gt; nil then
          begin
            SlKeysAct := TStringList.Create;
            SlKeysFor := TStringList.Create;
            try
              while not EOF do
              begin
                if AnsiUpperCase(FieldByName('Constraint_Type').AsString) =
                  AnsiUpperCase('FOREIGN KEY') then
                begin
                  SlKeysAct.Clear;
                  (* In einzelne Felder teilen *)
                  GetKeys(FieldByName('Constraint_Keys').AsString, True);
                  (* Constraint festhalten *)
                  sConstraintName := FieldByName('Constraint_Name').AsString;
                  (* Referenz steht im nächsten Datensatz *)
                  Next;
                  (* Tabelle und Felder auflösen *)
                  GetForeignKeyFieldsAndTable(FieldByName('Constraint_Keys').AsString);
                  (* In einzelne Felder teilen *)
                  SlKeysFor.Clear;
                  GetKeys(sForeignKeys, False);
                  for i := 0 to (SlKeysAct.Count - 1) do
                  begin
                    SlForeignKeyName.Add(sConstraintName);
                    SlKeysActTable.Add(SlKeysAct.Strings[i]);
                    SlKeysForeignTable.Add(SlKeysFor.Strings[i]);
                    SlForeignKeyTable.Add(sForeignTable);
                  end;
                end;
                Next;
              end;
            finally
              FreeAndNil(SlKeysAct);
              FreeAndNil(SlKeysFor);
            end;
          end;
        end;
      end;

    finally
      FreeAndNil(MyRecordQuery);
    end;
  finally
    FreeAndNil(MySelectQuery);
  end;
end;

procedure TForm1.BStartClick(Sender: TObject);
var
  SlForeignKeyName, SlKeysActTable, SlKeysForeignTable, SlForeignKeyTable: TStringList;
  i: Integer;
begin
  try
    SlForeignKeyName   := TStringList.Create;
    SlKeysActTable     := TStringList.Create;
    SlKeysForeignTable := TStringList.Create;
    SlForeignKeyTable  := TStringList.Create;
    GetForeignKeys('Kundendaten',      // Tabel name
      ADOConnection1,     // ADOConnection
      SlForeignKeyName,   // Foreign Key Name
      SlKeysActTable,      
      SlKeysForeignTable, 
      SlForeignKeyTable); 
    for i := 0 to (SlForeignKeyName.Count - 1) do
    begin
      if i &gt; 0 then
      begin
        Memo1.Lines.Add('');
      end;
      Memo1.Lines.Add(SlForeignKeyName.Strings[i]);
      Memo1.Lines.Add(SlKeysActTable.Strings[i]);
      Memo1.Lines.Add(SlKeysForeignTable.Strings[i]);
      Memo1.Lines.Add(SlForeignKeyTable.Strings[i]);
    end;
  finally
    FreeAndNil(SlForeignKeyName);
    FreeAndNil(SlKeysActTable);
    FreeAndNil(SlKeysForeignTable);
    FreeAndNil(SlForeignKeyTable);
  end;
end;

end.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

unit Unit1;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Grids, DBGrids, DB, ADODB, StdCtrls, DBCtrls;

type

TForm1 = class(TForm)

ADOConnection1: TADOConnection;

Memo1: TMemo;

BStart: TButton;

procedure BStartClick(Sender: TObject);

private

procedure GetForeignKeys(sTableName: string;

MyConnection: TADOConnection;

var SlForeignKeyName,

SlKeysActTable,

SlKeysForeignTable,

SlForeignKeyTable: TStringList);

public

{ Public-Deklarationen }

end;

var

Form1: TForm1;

implementation

{$R *.dfm}

(* Get foreign keys *)

procedure TForm1.GetForeignKeys(sTableName: string;

MyConnection: TADOConnection;

var SlForeignKeyName,

SlKeysActTable,

SlKeysForeignTable,

SlForeignKeyTable: TStringList);

var

MySelectQuery, MyRecordQuery: TADOQuery;

i: Integer;

SlKeysAct, SlKeysFor: TStringList;

sConstraintName, sForeignKeys, sForeignTable: string;

(* Schlüssel ermitteln *)

procedure GetKeys(sKeyList: string; fAct: Boolean);

var

i: Integer;

sKey: string;

begin

i := 0;

repeat

Inc(i);

if sKeyList[i] <> ',' then

begin

sKey := sKey + sKeyList[i];

end

else

begin

if fAct then

begin

SlKeysAct.Add(sKey);

end

else

begin

SlKeysFor.Add(sKey);

end;

if (Length(sKey) + 1) < i then

begin

if sKey[(i + 1)] = ' ' then

begin

Inc(i);

end;

sKey := '';

end;

until (i = Length(sKeyList));

if sKey <> '' then

begin

if fAct then

begin

SlKeysAct.Add(sKey);

end

else

begin

SlKeysFor.Add(sKey);

end;

procedure GetForeignKeyFieldsAndTable(sSQL: string);

var

i: Integer;

sValue: string;

iPos: Integer;

fAddValue: Boolean;

fInFields: Boolean;

begin

if Length(sSQL) >= 10 then

begin

(* REFERENCES entfernen *)

sValue := Copy(sSQL, 1, 10);

if AnsiUpperCase(sValue) = 'REFERENCES' then

begin

Delete(sSQL, 1, 11);

end;

i := 0;

iPos := 0;

sValue := '';

fInFields := False;

repeat

Inc(i);

fAddValue := False;

(* "normal" lesen *)

if (sSQL[i] <> '.') and

(sSQL[i] <> ' ') and

(sSQL[i] <> '(') and

(sSQL[i] <> ')') and

(fInFields = False) then

begin

sValue := sValue + sSQL[i];

fAddValue := True;

end;

(* In Felder *)

if sSQL[i] = '(' then

begin

fInFields := True;

end;

if (fInFields) and (sSQL[i] <> '(') and (sSQL[i] <> ')') then

begin

sValue := sValue + sSQL[i];

end;

(* Felder verlassen *)

if sSQL[i] = ')' then

begin

fInFields := False;

end;

if (fAddValue = False) and (fInFields = False) then

begin

case iPos of

(* Datenbank *)

begin

sValue := '';

Inc(iPos);

end;

(* Ower *)

begin

sValue := '';

Inc(iPos);

end;

(* Tabelle *)

begin

sForeignTable := sValue;

sValue := '';

Inc(iPos);

end;

(* Felder *)

begin

sForeignKeys := sValue;

sValue := '';

Inc(iPos);

end;

else

begin

end;

until (i = Length(sSQL));

end;

begin

try

MySelectQuery := TADOQuery.Create(Application);

with MySelectQuery do

begin

Name := 'MyHelpSelectQuery';

Connection := MyConnection;

SQL.Add('sp_help ' + sTableName);

Active := True;

end;

try

MyRecordQuery := TADOQuery.Create(Application);

with MySelectQuery do

begin

Name := 'MyHelpRecordQuery';

Connection := MyConnection;

Recordset := MySelectQuery.NextRecordset(i);

if MySelectQuery.State = dsBrowse then

begin

Recordset := MySelectQuery.NextRecordset(i);

if FindField('Constraint_Type') <> nil then

begin

SlKeysAct := TStringList.Create;

SlKeysFor := TStringList.Create;

try

while not EOF do

begin

if AnsiUpperCase(FieldByName('Constraint_Type').AsString) =

AnsiUpperCase('FOREIGN KEY') then

begin

SlKeysAct.Clear;

(* In einzelne Felder teilen *)

GetKeys(FieldByName('Constraint_Keys').AsString, True);

(* Constraint festhalten *)

sConstraintName := FieldByName('Constraint_Name').AsString;

(* Referenz steht im nächsten Datensatz *)

Next;

(* Tabelle und Felder auflösen *)

GetForeignKeyFieldsAndTable(FieldByName('Constraint_Keys').AsString);

(* In einzelne Felder teilen *)

SlKeysFor.Clear;

GetKeys(sForeignKeys, False);

for i := 0 to (SlKeysAct.Count - 1) do

begin

SlForeignKeyName.Add(sConstraintName);

SlKeysActTable.Add(SlKeysAct.Strings[i]);

SlKeysForeignTable.Add(SlKeysFor.Strings[i]);

SlForeignKeyTable.Add(sForeignTable);

end;

Next;

end;

finally

FreeAndNil(SlKeysAct);

FreeAndNil(SlKeysFor);

end;

finally

FreeAndNil(MyRecordQuery);

end;

finally

FreeAndNil(MySelectQuery);

end;

procedure TForm1.BStartClick(Sender: TObject);

var

SlForeignKeyName, SlKeysActTable, SlKeysForeignTable, SlForeignKeyTable: TStringList;

i: Integer;

begin

try

SlForeignKeyName := TStringList.Create;

SlKeysActTable := TStringList.Create;

SlKeysForeignTable := TStringList.Create;

SlForeignKeyTable := TStringList.Create;

GetForeignKeys('Kundendaten', // Tabel name

ADOConnection1, // ADOConnection

SlForeignKeyName, // Foreign Key Name

SlKeysActTable,

SlKeysForeignTable,

SlForeignKeyTable);

for i := 0 to (SlForeignKeyName.Count - 1) do

begin

if i > 0 then

begin

Memo1.Lines.Add('');

end;

Memo1.Lines.Add(SlForeignKeyName.Strings[i]);

Memo1.Lines.Add(SlKeysActTable.Strings[i]);

Memo1.Lines.Add(SlKeysForeignTable.Strings[i]);

Memo1.Lines.Add(SlForeignKeyTable.Strings[i]);

end;

finally

FreeAndNil(SlForeignKeyName);

FreeAndNil(SlKeysActTable);

FreeAndNil(SlKeysForeignTable);

FreeAndNil(SlForeignKeyTable);

end;

end.

Make a question and you'll have an answer…

Make a question and you'll have an answer…

Get all foreignkeys in use in a given table (MS SQL Server)