unit1.pas

sql版功能齐全

{作者：星五 [e-mail] www.e8e.net
 QQ:593106 加入消息：DF编程 或 DF编程爱好者
 申明：我是业余爱好者，并不专业，不要拿我比}
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    Duba: TADOQuery;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
   _s3,_f,_t:string;
   i,k:integer;
   _s:textfile;
   tname: array[0..7] of string  ;
begin
  tname[0]:='dlyb' ;
  tname[1]:='groupuser' ;
  tname[2]:='jyxxb'  ;
  tname[3]:='qxgl' ;
  tname[4]:='sx'  ;
  tname[5]:='tsjbxx';
  tname[6]:='tslb';
  tname[7]:='yh' ;
  for k:=0 to 7 do
    begin
      Randomize;
      _t:=tname[k];
      _s3:=ExtractFileDir(application.ExeName)+'\backup\'+_t+FormatDateTime('yyyymmddhhmmss',now())+'.sql';
      //备份文件名，取当前日期时间为文件名
      assignfile(_s,_s3);
      rewrite(_s);//新建并打开，文件存在则覆盖目标文件
      //_t:=edit1.Text;
      Duba.Active:=false;
      with Duba do
      begin
        close;
        sql.clear;
        sql.add('select syscolumns.name as 名称,systypes.name as 类型,syscolumns.prec as 长度,isnullable as 允许空 from syscolumns,systypes where (object_id('''+_t+''')=syscolumns.id) and (syscolumns.xusertype=systypes.xusertype)');
        //表的数据都存在库的syscolumns表里，所以只要读它就能取出表结构，systypes表里的是类型
        open;
      end;
      Duba.Active:=true;
      if Duba.RecordCount>0 then
      begin
        Duba.First;
        writeln(_s,'--表'''+_t+'''的结构');
        writeln(_s,'  if exists (select * from sysobjects where id = OBJECT_ID('''+_t+''') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) DROP TABLE '+_t);
        write(_s,'  CREATE TABLE '+_t+'(');
        repeat
          if duba.FieldByName('类型').AsString='nvarchar' then
          begin
          for i:=1 to Duba.FieldCount-1 do
            if duba.Fields.FieldByNumber(i).DataType=ftSmallint then write(_s,'('+Duba.Fields.FieldByNumber(i).AsString+') ')
              else write(_s,duba.Fields.FieldByNumber(i).asString+' ');
          end else
          for i:=1 to Duba.FieldCount-1 do
            if duba.Fields.FieldByNumber(i).DataType<>ftSmallint then write(_s,duba.Fields.FieldByNumber(i).asString+' ');
          if Duba.Fields.FieldByNumber(Duba.FieldCount).AsString='1' then write(_s,'null,')
            else write(_s,'not null,');
          Duba.Next;
        until Duba.Eof;
        write(_s,')');
        writeln(_s);
      end;
      Duba.Active:=false;
      with Duba do
      begin
        close;
        sql.clear;
        sql.add('SELECT * FROM '+_t);
        open;
      end;
      Duba.Active:=true;
      if Duba.RecordCount>0 then
      begin
        _f:='';
        for i:=1 to Duba.FieldCount-1 do
          _f:=_f+Duba.Fields.FieldByNumber(i).FieldName+',';
        _f:=_f+Duba.Fields.FieldByNumber(Duba.FieldCount).FieldName;
        Duba.First;
        writeln(_s,'--表'''+_t+'''的数据');
        repeat
          write(_s,'  insert into '+_t+'('+_f+ ') Values(');
          for i:=1 to Duba.FieldCount-1 do
            if Duba.Fields.FieldByNumber(i).AsString='' then write(_s,'null,')
              else if (Duba.Fields.FieldByNumber(i).DataType=ftWideString) or (Duba.Fields.FieldByNumber(i).DataType=ftDateTime) then write(_s,''''+Duba.Fields.FieldByNumber(i).AsString+''',')
                else write(_s,Duba.Fields.FieldByNumber(i).AsString+',');
          if Duba.Fields.FieldByNumber(Duba.FieldCount).AsString='' then write(_s,'null)')
            else if (Duba.Fields.FieldByNumber(Duba.FieldCount).DataType=ftWideString) or (Duba.Fields.FieldByNumber(Duba.FieldCount).DataType=ftDateTime) then write(_s,''''+Duba.Fields.FieldByNumber(Duba.FieldCount).AsString+''')')
              else write(_s,Duba.Fields.FieldByNumber(Duba.FieldCount).AsString+')');
            Writeln(_s);
          Duba.Next;
        until Duba.Eof;
      end;
      Closefile(_s);
    end;
  MessageBox(Handle,'备份完成','提示',MB_OK or MB_ICONINFORMATION);
end;

end.