我使用的資料庫是SQL Server 2000,我想在程式中判斷某一個表格的欄位是否存在,若不存在的話,則下SQL指令去新增,請問我該怎麼作呢?謝謝!

先 Import Type Library 選擇 Microsoft ADO Ext. 2.1 for DLL and Security(Version 2.1) 此時下方 Class names 的 ListBox 中的 TTable/TColumn/TIndex 改成 TADOXTable/TADOXColumn/TADOXIndex 然後點 Install 鈕. 如此在元件盤的 ActiveX 就會產生 Catalog/ADOXTable ...等元件 以下示範如何取得資料庫內的所有 Table 與各 Table 的欄位    

unit Unit1;
   
interface
   
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, OleServer, ComObj, Db, ADODB, ADOX_TLB, Grids;
   
type
  TForm1 = class(TForm)
    btnOpenSchema: TButton;
    Catalog1: TCatalog;
    ADOConnection1: TADOConnection;
    ADOXTable1: TADOXTable;
    lbTables: TListBox;
    btnGetTables: TButton;
    StringGrid1: TStringGrid;
    btnGetFields: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnGetTablesClick(Sender: TObject);
    procedure lbTablesClick(Sender: TObject);
    procedure btnGetFieldsClick(Sender: TObject);
  private
    function GetADOXFieldType(aType: DataTypeEnum): String;
  public
    { Public declarations }
  end;
   
var
  Form1: TForm1;
  adoxCatalog: _Catalog;
  adoxTables: Tables;
  adoxTable: _Table;
  adoxColumns: Columns;
  adoxColumn: _Column;
   
implementation
   
{$R *.DFM}
   
procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.Connected := True;
  adoxCatalog := CoCatalog.Create;
  StringGrid1.Cells[0, 0] := 'Field Name';
  StringGrid1.Cells[1, 0] := 'Field Type';
end;
   
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOConnection1.Close;
  adoxCatalog := nil;
end;
  
procedure TForm1.btnGetTablesClick(Sender: TObject);
var i: integer;
begin
  lbTables.Clear;
  adoxCatalog.Set_ActiveConnection(ADOConnection1.ConnectionObject);
  adoxTables := adoxCatalog.Tables;
  for i := 0 to adoxTables.Count -1 do
    lbTables.Items.Add(adoxTables.Item[i].Name);
end;
   
procedure TForm1.lbTablesClick(Sender: TObject);
begin
  adoxTable := adoxCatalog.Tables.Item[lbTables.ItemIndex];
end;
   
procedure TForm1.btnGetFieldsClick(Sender: TObject);
var i,j: integer;
begin
  for i := 0 to StringGrid1.ColCount -1 do
    for j := 1 to StringGrid1.RowCount -1 do
      StringGrid1.Cells[i, j] := '';      adoxColumns := adoxTable.Columns;
  for i := 0 to adoxColumns.Count -1 do begin
    adoxColumn := adoxColumns.Item[i];
    StringGrid1.Cells[0, i 1] := adoxColumn.Name;
    StringGrid1.Cells[1, i 1] := GetADOXFieldType(adoxColumn.Type_);
  end;
end;
   
function TForm1.GetADOXFieldType(aType: DataTypeEnum): String;
begin
  case aType of    //
    adEmpty : Result := 'adEmpty';
    adTinyInt : Result := 'adTinyInt';
    adSmallInt : Result := 'adSmallInt';
    adInteger : Result := 'adInteger';
    adBigInt : Result := 'adBigInt';
    adUnsignedTinyInt : Result := 'adUnsignedTinyInt';
    adUnsignedSmallInt : Result := 'adUnsignedSmallInt';
    adUnsignedInt : Result := 'adUnsignedInt';
    adUnsignedBigInt : Result := 'adUnsignedBigInt';
    adSingle : Result := 'adSingle';
    adDouble : Result := 'adDouble';
    adCurrency : Result := 'adCurrency';
    adDecimal : Result := 'adDecimal';
    adNumeric : Result := 'adNumeric';
    adBoolean : Result := 'adBoolean';
    adError : Result := 'adError';
    adUserDefined : Result := 'adUserDefined';
    adVariant : Result := 'adVariant';
    adIDispatch : Result := 'adIDispatch';
    adIUnknown : Result := 'adIUnknown';
    adGUID : Result := 'adGUID';
    adDate : Result := 'adDate';
    adDBDate : Result := 'adDBDate';
    adDBTime : Result := 'adDBTime';
    adDBTimeStamp : Result := 'adDBTimeStamp';
    adBSTR : Result := 'adBSTR';
    adChar : Result := 'adChar';
    adVarChar : Result := 'adVarChar';
    adLongVarChar : Result := 'adLongVarChar';
    adWChar : Result := 'adWChar';
    adVarWChar : Result := 'adVarWChar';
    adLongVarWChar : Result := 'adLongVarWChar';
    adBinary : Result := 'adBinary';
    adVarBinary : Result := 'adVarBinary';
    adLongVarBinary : Result := 'adLongVarBinary';
    adChapter : Result := 'adChapter';
    adFileTime : Result := 'adFileTime';
    adPropVariant : Result := 'adPropVariant';
    adVarNumeric : Result := 'adVarNumeric';
  end;    // case
end;
      
end.

--- 每個人都是一本書

select * from INFORMATION_SCHEMA.COLUMNS 可以取得某個database裡的整個table資訊.... 再下where去檢查某欄位是否存在就好囉.....

引言: 我使用的資料庫是SQL Server 2000,我想在程式中判斷某一個表格的欄位是否存在,若不存在的話,則下SQL指令去新增,請問我該怎麼作呢?謝謝! < face="Verdana, Arial, Helvetica">

if (select count(*) from sysobjects o,syscolumns c where c.id=o.id and c.name='c2' and o.name='test' and o.type ='U')=0 alter table test add c2 int Note : 1. table name : "test" 2. new column name : "c2"

其實一個簡單的想法, 不會用到系統的資料表, 直接下 command 來找就好了.. 幾位前輩的方式都不錯, 不過可以試一下這個: 要找的 table 名為 T1, 要找的 column name 為 some_col. 若找不到的話, 新增 some_col 的 field type 為 int

procedure TForm1.Button1Click(Sender: TObject);
var
  i: integer;
  bHave: boolean;
begin
  Query1.Close;
  Query1.SQL.Text := 'SELECT * FROM T1 WHERE 1=2';
  Query1.Open;
  bHave := False;
  For i:=0 to Query1.FieldCount -1 do
  begin
    If Query1.Fields[i].FieldName = 'some_col' Then
    begin
      bHave := True;
      break;
    end;
  end;
  Query1.Close;
  //
  If not bHave Then
  begin
    Query1.SQL.Text := 'ALTER TABLE T1 ADD COLUMN some_col int';
    Query1.ExecSQL;
  end;
end;

這種方法的好處是可以適用於多種資料庫..