寻求复制表结构和所有约束的最简单办法

Question

select * into 臨時表 from 原表

Answer 1

在企业管理器中生成SQL脚本，再改表名运行一次即可。

Answer 2

把现有的表生成.SQL脚本，在脚本中将现有表的表名改为你要的临时表，再运行该脚本

Answer 3

procedure TFrmMain.TrVwChange(Sender: TObject; Node: TTreeNode);
var
    Ds:TAdoDataSet;
    i,j:integer;
begin
    if (Node=TrVw.Items[0]) or (Node.Parent<>TrVw.Items[0]) then
    begin
        Grid.RowCount:=2;
        Grid.ColCount:=2;
        For i:=0 to Grid.ColCount-1 do Grid.Cells[i,0]:='';
        For i:=0 to Grid.ColCount-1 do Grid.Cells[i,1]:='';
        if (Node<>TrVw.Items[0]) then
        begin
            Ds:=TAdoDataSet.Create(nil);
            try
                Ds.Connection:=AdoConn;
                Ds.CommandText:=' select'+
                                ' 序号=a.colorder,'+
                                ' 字段名称=a.name,'+
                                ' 标识=case when columnproperty( a.id,a.name,''isidentity'')=1 then ''√''else '''' end,'+
                                ' 主键=case when exists(select 1 from sysobjects where xtype=''pk'' and name in'+
                                '                      (select name from sysindexes where indid in'+
                                '                      (select indid from sysindexkeys where id = a.id and colid=a.colid)))'+
                                '                      then ''√'' else '''' end,'+
                                ' 类型=b.name,'+
                                ' 占用字节数=a.length,'+
                                ' 长度=columnproperty(a.id,a.name,''precision''),'+
                                ' 小数位数=isnull(columnproperty(a.id,a.name,''scale''),0),'+
                                ' 允许空=case when a.isnullable=1 then ''√'' else '''' end,'+
                                ' 默认值=isnull(e.text,''''),'+
                                ' 字段说明=isnull(g.[value],''''),'+
                                ' 索引名称=isnull(h.索引名称,''''),'+
                                ' 索引顺序=isnull(h.排序,'''')'+
                                ' from syscolumns a'+
                                ' left join systypes b on a.xtype=b.xusertype'+
                                ' left join sysobjects d on a.id=d.id  and d.xtype=''u'' and d.status>=0'+
                                ' left join syscomments e on a.cdefault=e.id'+
                                ' left join sysproperties g on a.id=g.id and a.colid=g.smallid'+
                                ' left join sysproperties f on d.id=f.id and f.smallid=0'+
                                ' left join('+
                                ' select 索引名称=a.name,c.id,d.colid,排序=case indexkey_property(c.id,b.indid,b.keyno,''isdescending'')when 1 then ''降序'' when 0 then ''升序'' end'+
                                ' from sysindexes a join sysindexkeys b on a.id=b.id and a.indid=b.indid'+
                                ' join (select id,colid,indid=min(indid) from sysindexkeys group by id,colid) b1 on b.id=b1.id and b.colid=b1.colid and b.indid=b1.indid'+
                                ' join sysobjects c on b.id=c.id and c.xtype=''u'' and c.status>=0'+
                                ' join syscolumns d on b.id=d.id and b.colid=d.colid'+
                                ' where a.indid not in(0,255)'+
                                ' ) h on a.id=h.id and a.colid=h.colid'+
                                ' where d.name='''+Node.Parent.Text+''''+
                                ' order by a.id,a.colorder';
                DS.CommandTimeout:=0;
                Ds.Open;
                Grid.RowCount:=2;
                Grid.FixedRows:=1;
                Grid.FixedCols:=1;
                Grid.ColCount:=Ds.FieldDefs.Count;
                For i := 0 To Grid.ColCount - 1 do
                begin
                    Grid.Cells[i,0]:=Ds.FieldDefs[i].Name;
                    Grid.Cells[i,1]:='';
                end;
                if not Ds.Eof or not Ds.Bof then
                begin
                    Ds.First;
                    FlatGauge1.MaxValue:=Ds.RecordCount;
                    FlatGauge1.Progress:=0;
                    FlatGauge1.Visible:=True;
                    While Not Ds.EOF do
                    begin
                        For i := 0 To Grid.ColCount - 1 do
                            Grid.Cells[i,Grid.RowCount-1]:=Ds.Fields[i].AsString;
                        Ds.Next;
                        FlatGauge1.Progress:=FlatGauge1.Progress+1;
                        Grid.RowCount:=Grid.RowCount+1;
                        for i:=0 to Grid.ColCount-1 do
                            Grid.Cells[i,Grid.RowCount-1]:='';
                    end;
                    Grid.RowCount:=Grid.RowCount-1;
                end;
            finally
                Ds.Close;
                Ds.Free;
                FlatGauge1.Visible:=False;
            end;
        end;
    end
    else
    begin
        Ds:=TAdoDataSet.Create(nil);
        try
            Ds.Connection:=AdoConn;
            try
                AdoConn.BeginTrans;
                case FindType of
                -1:exit;
                0,1:AdoConn.Execute('select identity(int,1,1) as id,* into #t from '+Node.Text);
                2:begin
                    if IsText then AdoConn.Execute('select identity(int,1,1) as id,* into #t from '+Node.Text+ ' where isnull('+Field+','''')=''''')
                    else AdoConn.Execute('select identity(int,1,1) as id,* into #t from '+Node.Text+ ' where isnull('+Field+',0)=0');
                  end;
                3:begin
                    if IsText then AdoConn.Execute('select identity(int,1,1) as id,* into #t from '+Node.Text+ ' where '+Field+'='''+Value+'''')
                    else AdoConn.Execute('select identity(int,1,1) as id,* into #t from '+Node.Text+ ' where '+Field+'='+String(Value));
                 end;
                end;
                Ds.CommandText:='select * from #t order by id';
                DS.CommandTimeout:=0;
                Ds.Open;
                Grid.RowCount:=2;
                Grid.FixedRows:=1;
                Grid.FixedCols:=1;
                Grid.ColCount:=Ds.FieldDefs.Count;
                For i := 0 To Grid.ColCount - 1 do
                begin
                    Grid.Cells[i,0]:=Ds.FieldDefs[i].Name;
                    Grid.Cells[i,1]:='';
                end;
                if not Ds.Eof or not Ds.Bof then
                begin
                    Ds.First;
                    FlatGauge1.MaxValue:=Ds.RecordCount;
                    FlatGauge1.Progress:=0;
                    FlatGauge1.Visible:=True;
                    While Not Ds.EOF do
                    begin
                        For i := 0 To Grid.ColCount - 1 do
                            Grid.Cells[i,Grid.RowCount-1]:=Ds.Fields[i].AsString;
                        Ds.Next;
                        FlatGauge1.Progress:=FlatGauge1.Progress+1;
                        Grid.RowCount:=Grid.RowCount+1;
                        for i:=0 to Grid.ColCount-1 do
                            Grid.Cells[i,Grid.RowCount-1]:='';
                    end;
                    Grid.RowCount:=Grid.RowCount-1;
                end;
                AdoConn.Execute('drop table #t');
                AdoConn.CommitTrans;
            except
                AdoConn.RollbackTrans;
                raise;
            end;
        finally
            Ds.Close;
            Ds.Free;
            FlatGauge1.Visible:=False;
        end;
    end;
    AutosetGrid(Grid);
end;自己写的数据精灵中的部分代码，楼主需要留下EMAIL

Answer 4

callzjy写得太多，我还是觉得SELECT　＊　INTO。来得最快最爽。

Answer 5

SELECT　＊　INTO。只能复制表结构，和数据,但索引,约束好象不行把

Answer 6

不是好像不行,SELECT　＊　INTO 是肯定不能复制索引,约束

Answer 7

SELECT　＊　INTO 是肯定不能复制索引,约束,因为它们的存储机制是与数据库不一样的，所以，还要进行加工才能符合您的要求，比如楼上说的把代码从新执行一下也好（表名是您需增加的那个哦）