根据原表结构形成建表代码

[huangqing_80]

已知一表tb,列名和类型、长度如下
列名 类型 长度 精度 小数位数
sid int 4 0 0
spbh char 20 0 0
lshj decimal 9 14 2
ishysp bit 2 0 0
…
现在我想根据该表的结构（列名和列属性，暂不考虑其它），写一段建表的代码，例如
create table tbtest(sid int,spbh char(20),lshj decimal(14,2),……
我自己想了一下，如果列为char的倒是可以，但若为int或者decimal的就不好写，现想请哪位
大侠看一下。
另外，如果能够实现，还可以引申出动态构建表的代码，也可以考虑一下如果有其它对象存在的话，如何
实现，比如说索引、约束、触发器等对象
另外，在实际开发过程中，有时会碰到数据库从一个老版本更新到新版本，但通常表名不会发生变化，但表
结构会发生变化，如字段加长或增加一些触发器等对象，那么这个思想还可以引申到如何比较新旧数据库之间的
差异等等。
期待各位在此一解。

[sgtzzc]

用case when

[huangqing_80]

另外，我的数据库环境是SQL Server 2005,系统是XP

[SQL77]

学习

[huangqing_80]

楼上说的有些简单，具体的思路是怎样的，期待楼下的

[--小F--]

****************************************************************************
 软件名称: May Flower Erp
 版权所有: (C) 2005-2006 May Flower ERP 开发组
 功能描述: 获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)
----------------------------------------------------------------------------
 参数列表: 
        1: @TableName 需要创建脚本的表的名称
****************************************************************************
|</PRE>*/
CREATE PROCEDURE [dbo].[sp_HelpTable](@TableName sysname)
AS
  SET NOCOUNT ON
  DECLARE @ObjectID int
  DECLARE @TableScript table(Iden int IDENTITY(1, 1), ScriptLine nvarchar(4000))
  SET @ObjectID = object_id(@TableName)
  IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0
  BEGIN
    RAISERROR('指定的对象不是表对象', 16, 1)
    RETURN
  END 
  --获取表的创建脚本
  --插入表头
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'CREATE TABLE [' + USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].[' + object_name(@ObjectID) + N']('

  --插入字段
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'  [' + a.Name + N'] [' + b.name + N']' + 
           CASE WHEN c.Object_id IS NOT NULL THEN N' IDENTITY(' + CONVERT(nvarchar, c.seed_value) + N', ' + CONVERT(nvarchar, c.increment_value) + N')' 
                ELSE '' END +
           CASE WHEN b.xusertype IN (167, 175, 231, 239) THEN N'('+CONVERT(nvarchar, a.prec)  + N')'
                WHEN b.xusertype in (106, 108)           THEN N'('+CONVERT(nvarchar, a.xprec) + N', ' + CONVERT(nvarchar, a.xscale) + N')'
                ELSE '' END +
           CASE a.isnullable WHEN 1 THEN N'' ELSE N' NOT' END + N' NULL' +
           CASE WHEN d.Name IS NOT NULL THEN N' DEFAULT ' + d.Definition ELSE N'' END +
           N',' 
      FROM sys.syscolumns a
        LEFT JOIN sys.systypes b ON a.xusertype = b.xusertype
        LEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColID
        LEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID
    WHERE a.[ID] = @ObjectID
    ORDER BY a.ColOrder

  --插入主键和索引
  DECLARE @IndexID int, @IndexScript nvarchar(4000)
  DECLARE IndexCursor CURSOR FOR 
    SELECT b.Index_ID, N'  CONSTRAINT [' + a.Name + N'] ' + 
           CASE a.Type WHEN 'PK' THEN N'PRIMARY KEY ' WHEN 'UQ' THEN N'UNIQUE ' END + 
           CASE b.Type WHEN 1    THEN N'CLUSTERED'   WHEN 2     THEN N'NONCLUSTERED ' END + N'('
      FROM sys.key_constraints a
        LEFT JOIN sys.indexes b ON b.Object_ID = a.Parent_Object_ID AND b.index_id = a.unique_index_id
    WHERE a.Parent_Object_ID = @ObjectID
  OPEN IndexCursor
  FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @IndexScript = @IndexScript + N'[' + INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'
      FROM sys.index_columns 
    WHERE Object_ID = @ObjectID
      AND Index_ID = 2
    
    SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) -1) + N'),'
    INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)
    FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  END
  Close IndexCursor
  DEALLOCATE IndexCursor

  insert into @TableScript(ScriptLine) VALUES(')')
  select * from @TableScript 



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/10/14/4669511.aspx

[huangqing_80]

小F的回答对于单表是正确的，如果要创建多表呢，用游标可以实现吗

[huangqing_80]

另外，如何比较两个数据库之间的差异呢，如果逐个比对，显然效率非常低下，用游标可以实现吗

[huangqing_80]

期待更佳的答案

[--小F--]

多表的实在是不会

[wanglaibing]

SQL Server 2005?
我现在一直都是在用2000

[sgtzzc]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列名] varchar(6),[类型] varchar(7),[长度] int,[精度] int,[小数位数] int)
insert [tb]
select 'sid','int',4,0,0 union all
select 'spbh','char',20,0,0 union all
select 'lshj','decimal',9,14,2 union all
select 'ishysp','bit',2,0,0
 
--->查询
declare @sql varchar(8000)
select 
  @sql=isnull(@sql+',','')
      +列名+' '+类型
      +case 
         when 类型='int' then ''
         when 类型='char' then '('+ltrim(长度)+')'
         when 类型='decimal' then '('+ltrim(长度)+','+ltrim(小数位数)+')'
         when 类型='bit' then ''
       end
from tb

exec ('create table tbtest('+@sql+')')

insert tbtest select 1,'001',12.3,1

select * from tbtest

/**
sid         spbh                 lshj        ishysp 
----------- -------------------- ----------- ------ 
1           001                  12.30       1

（所影响的行数为 1 行）
**/

举个例子，类型还可以罗列

[sgtzzc]

[Quote=引用 6 楼 huangqing_80 的回复:]
小F的回答对于单表是正确的，如果要创建多表呢，用游标可以实现吗
[/Quote]

多表的就你给的数据和表结构实现不了，要加一个表名的字段

[huangqing_80]

比如说我现在就碰到这样一个问题，
公司原有的系统是北京时空公司的KSOA，但当时上系统时并没有考虑周全，它并不适合公司的生产流程，现在想将KSOA更换成北京时空的另一套系统STIB，两套系统之间很多表是相同的，不同的就是有些表的结构可能会发生了变化，如果手工检查，效率很低，所以我就有了这样一个想法，比较两套系统之间表的差异，并且将有差异的表进行相关处理，比如说STIB中的SPKFK表中的SPBH字段长度是20，而KSOA是24，这就需要修改STIB数据库表SPKFK中SPBH的长度，这才有了这样一个想法

[xingtong]

huangqing_80,我抓住你了

[huangqing_80]

嗯，四方城说的没错，如果要求所有，还需要表名，那么，小F的思路就要更加深一点了
期待更佳的答案

[sgtzzc]

[Quote=引用 13 楼 huangqing_80 的回复:]
比如说我现在就碰到这样一个问题，
公司原有的系统是北京时空公司的KSOA，但当时上系统时并没有考虑周全，它并不适合公司的生产流程，现在想将KSOA更换成北京时空的另一套系统STIB，两套系统之间很多表是相同的，不同的就是有些表的结构可能会发生了变化，如果手工检查，效率很低，所以我就有了这样一个想法，比较两套系统之间表的差异，并且将有差异的表进行相关处理，比如说STIB中的SPKFK表中的SPBH字段长度是20，而KSOA是24，这就需要修改STIB数据库表SPKFK中SPBH的长度，这才有了这样一个想法
[/Quote]

不建议这么做，这样做完以后，还是一样要检查，不检查你也不知道哪些表要改，改成功了没有，就意味着风险

[huangqing_80]

无枪狙击手、邹建、海爷、大乌龟等各位大侠，如果你们在线的话，也希望能给我一些提示和启发，也希望在线的其他高手朋友看一下这个贴子

[huangqing_80]

四方城，改完后结构比较的代码我倒是可以写出来，当然在上生产线之前我一定会进行比较的

[xingtong]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SBP_DbStruCompare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SBP_DbStruCompare]
go

create PROCEDURE [dbo].[SBP_DbStruCompare] -- 数据库表结构比较
@DBNameS varchar(40),
@DBNameD varchar(40)
as

create table #tmp_keys
(
keyname varchar(256),
fdname varchar(256)
)
create table #tmp_keyd
(
keyname varchar(256),
fdname varchar(256)
)

exec('declare @tbname varchar(256), @colname varchar(256), @indid int, @i int' +
' declare cursor_key INSENSITIVE CURSOR' +
' for select a.name as tbname, b.name as colname, c.indid ' +
' from ' + @DBNameS + '.dbo.sysobjects a join ' + @DBNameS + '.dbo.syscolumns b on a.id = b.id ' +
' join ' + @DBNameS + '.dbo.sysindexes c on a.id = c.id and (c.status & 0x800) > 0 and c.IndID > 0 and c.IndID <> 255 and left(c.name,7)<>''_WA_Sys''' +
' where a.type = ''u''and a.name = ''kjkmfse''' +
' open cursor_key' +
' fetch next from cursor_key into @tbname, @colname, @indid' +
' while (@@FETCH_STATUS <> -1)' +
' begin'+
' set @i = 1'+
' while @i <= 16'+
' begin '+
' if @colname = index_col(''' + @DBNameS + '.dbo.''+@tbname, @indid, @i)'+
' begin'+
' insert into #tmp_keys(keyname, fdname) values(@tbname, @colname)'+
' break'+
' end'+
' set @i = @i + 1'+
' end'+
' fetch next from cursor_key into @tbname, @colname, @indid'+
' end'+
' close cursor_key'+
' deallocate cursor_key')

exec('declare @tbname varchar(256), @colname varchar(256), @indid int, @i int' +
' declare cursor_key INSENSITIVE CURSOR' +
' for select a.name as tbname, b.name as colname, c.indid ' +
' from ' + @DBNameD + '.dbo.sysobjects a join ' + @DBNameD + '.dbo.syscolumns b on a.id = b.id ' +
' join ' + @DBNameD + '.dbo.sysindexes c on a.id = c.id and (c.status & 0x800) > 0 and c.IndID > 0 and c.IndID <> 255 and left(c.name,7)<>''_WA_Sys''' +
' where a.type = ''u''and a.name = ''kjkmfse''' +
' open cursor_key' +
' fetch next from cursor_key into @tbname, @colname, @indid' +
' while (@@FETCH_STATUS <> -1)' +
' begin'+
' set @i = 1'+
' while @i <= 16'+
' begin '+
' if @colname = index_col(''' + @DBNameD + '.dbo.''+@tbname, @indid, @i)'+
' begin'+
' insert into #tmp_keyd(keyname, fdname) values(@tbname, @colname)'+
' break'+
' end'+
' set @i = @i + 1'+
' end'+
' fetch next from cursor_key into @tbname, @colname, @indid'+
' end'+
' close cursor_key'+
' deallocate cursor_key')

exec('select a.tbname as tbnamea, a.fdname as fdnamea, a.fldtype as fldtypea, ' +
' a.fldsize as fldsizea, a.scale as scalea, a.defvalue as defvaluea, ' +
' a.isnullable as nulla, a.keyname as keynamea,' +
' b.tbname as tbnameb, b.fdname as fdnameb, b.fldtype as fldtypeb, ' +
' b.fldsize as fldsizeb, b.scale as scaleb, b.defvalue as defvalueb,' +
' b.isnullable as nullb, b.keyname as keynameb' +
' into #tmp_comptab ' +
' from ' +
'(select c.name as tbname, a.name as fdname,b.name as fldtype, isnull(a.prec, '''') as fldsize,isnull(a.scale, '''') as scale, ' +
' case a.cdefault when 0 then '''' else d.Text end defvalue, isnullable, isnull(e.keyname,'''') as keyname' +
' from ' + @DBNameS + '.dbo.syscolumns a join ' + @DBNameS + '.dbo.systypes b on a.xusertype = b.xusertype ' +
' join ' + @DBNameS + '.dbo.sysobjects c on a.id = c.id and c.type = ''u''' +
' left join #tmp_keys e on c.name = e.keyname and a.name = e.fdname' +
' left join ' + @DBNameS + '.dbo.syscomments d on a.cdefault = d.id ) a' +
' full join ' +
' (select c.name as tbname, a.name as fdname,b.name as fldtype, isnull(a.prec, '''') as fldsize,isnull(a.scale, '''') as scale, ' +
' case a.cdefault when 0 then '''' else d.Text end defvalue, isnullable, isnull(e.keyname,'''') as keyname' +
' from ' + @DBNameD + '.dbo.syscolumns a join ' + @DBNameD + '.dbo.systypes b on a.xusertype = b.xusertype ' +
' join ' + @DBNameD + '.dbo.sysobjects c on a.id = c.id and c.type = ''u''' +
' left join #tmp_keyd e on c.name = e.keyname and a.name = e.fdname' +
' left join ' + @DBNameD + '.dbo.syscomments d on a.cdefault = d.id ) b ' +
' on a.tbname = b.tbname and a.fdname = b.fdname and a.fldtype = b.fldtype and ' +
' a.scale = b.scale and a.defvalue = b.defvalue and a.isnullable = b.isnullable and a.keyname = b.keyname ' +
' where a.tbname is null or b.tbname is null ' +

' select isnull(a.tbnamea,a.tbnameb) as tbname, isnull(a.fdnamea,a.fdnameb) as fdname, isnull(a.fldtypea,a.fldtypeb) as fldtype,' +
' isnull(a.fldsizea,a.fldsizeb) as fldsize, isnull(a.scalea,a.scaleb) as scale, isnull(a.defvaluea,a.defvalueb) as defvalue, ' +
' case isnull(a.nulla,a.nullb) when 0 then '''' else ''√'' end as nullable,' +
' case when isnull(isnull(a.keynamea,a.keynameb),'''') <> '''' then ''√'' end as keyname, ' +
' c.name as tba, b.name as fda, d.name as tbb, e.name as fdb, ' +
' case when a.tbnamea is null then ''' + @DBNameD + ''' else ''' + @DBNameS + ''' end as dbname,' +
' b.usertype as usertypa, e.usertype as usertypb, b.prec as preca, e.prec as precb, b.scale as scalea, e.scale as scaleb,' +
' f.text as cdefaulta, g.text as cdefaultb, b.isnullable as nulla, e.isnullable as nullb, h.keyname as keynamea, i.keyname as keynameb' +
' from #tmp_comptab a left join ' + @DBNameS + '.dbo.sysobjects c on isnull(a.tbnamea, a.tbnameb) = c.name ' +
' left join ' + @DBNameS + '.dbo.syscolumns b on isnull(a.fdnamea, a.fdnameb) = b.name and b.id = c.id ' +
' left join ' + @DBNameS + '.dbo.syscomments f on b.cdefault = f.id ' +
' left join #tmp_keys h on isnull(a.tbnamea, a.tbnameb) = h.keyname and isnull(a.fdnamea, a.fdnameb) = h.fdname' +
' left join ' + @DBNameD + '.dbo.sysobjects d on isnull(a.tbnamea, a.tbnameb) = d.name ' +
' left join ' + @DBNameD + '.dbo.syscolumns e on isnull(a.fdnamea, a.fdnameb) = e.name and e.id = d.id ' +
' left join ' + @DBNameD + '.dbo.syscomments g on e.cdefault = g.id ' +
' left join #tmp_keyd i on isnull(a.tbnamea, a.tbnameb) = i.keyname and isnull(a.fdnamea, a.fdnameb) = i.fdname' +
' order by case when a.tbnamea is null then a.tbnameb else a.tbnamea end, ' +
' case when a.fdnamea is null then a.fdnameb else a.fdnamea end,13')