MS SQLSERVER 中如何得到表的创建语句

原创 2004年08月24日 19:19:00

在MS SQLSERVER 中,可以用

sp_helptext procedureName得到存储过程的创建语句。但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考.

该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句.

SQLSERVER2000 下的代码

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128)       /* The table to generate sql script */
as

declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID   TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length   TinyInt
declare @Prec     TinyInt
declare @Scale    TinyInt
declare @Status   TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID     SmallInt 
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName    varchar(30)
declare @strPri_Key varchar (255)

/*
**  Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
  select @DBName = db_name()
    raiserror(15009,-1,-1,@ObjName,@DBName)
    return (1)
end

create table #spscript
(
    id     int IDENTITY not null,
    Script Varchar(255) NOT NULL,
    LastLine tinyint
)

declare Cursor_Column INSENSITIVE CURSOR
  for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
        case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
        from syscolumns a, systypes b where object_name(a.id) = @ObjName
        and a.usertype = b.usertype order by a.ColID

set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)

/* Get column information */
open Cursor_Column

fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key

Select @Script = '
while (@@FETCH_STATUS <> -1)
begin
  if (@@FETCH_STATUS <> -2)
  begin
    Select @Script = @ColName + ' ' + @TypeName
    if @UserType in (1,2,3,4)
      Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
    else if @UserType in (24)
      Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
                      + Convert(char(3),@Scale) + ') '
    else
      Select @Script = @Script + ' '
    if ( @Status & 0x80 ) > 0
      Select @Script = @Script + ' IDENTITY(1,1) '

    if ( @Status & 0x08 ) > 0
      Select @Script = @Script + ' NULL '
    else
      Select @Script = @Script + ' NOT NULL '
    if @cDefault > 0
      Select @Script = @Script + ' DEFAULT ' + @Const_Key
  end
  fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key
  if @@FETCH_STATUS = 0
  begin
    Select @Script = @Script + ','
    Insert into #spscript values(@Script,0)
  end
  else
  begin
    Insert into #spscript values(@Script,1)
    Insert into #spscript values(')',0)
  end
end
Close Cursor_Column
Deallocate Cursor_Column

/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
  for Select name,IndID,status from sysindexes where object_name(id)=@ObjName
              and IndID > 0 and IndID<>255  order by IndID   /*增加了对InDid为255的判断*/
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
  if @@FETCH_STATUS <> -2
  begin

    declare @i TinyInt
    declare @thiskey varchar(50)
    declare @IndDesc varchar(68) /* string to build up index desc in */

    Select  @i = 1
    while (@i <= 16)
    begin
      select @thiskey = index_col(@ObjName, @IndID, @i)
      if @thiskey is null
        break

      if @i = 1
        select @Index_Key = index_col(@ObjName, @IndID, @i)
      else
        select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
      select @i = @i + 1
    end
    if (@IndStatus & 0x02) > 0
      Select @Script = 'Create unique '
    else
      Select @Script = 'Create '
    if @IndID = 1
      select @Script = @Script + ' clustered '


    if (@IndStatus & 0x800) > 0
     select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
    else
     select @strPri_Key = '
     
    if @IndID > 1
      select @Script = @Script + ' nonclustered '
    Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
           + '(' + @Index_Key + ')'
    Select @IndDesc = '
    /*
 **  See if the index is ignore_dupkey (0x01).
    */
    if @IndStatus & 0x01 = 0x01
      Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
    /*
     **  See if the index is ignore_dup_row (0x04).
    */
   /* if @IndStatus & 0x04 = 0x04 */
   /*   Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */ /* 2000 不在支持*/
    /*
 **  See if the index is allow_dup_row (0x40).
    */
    if @IndStatus & 0x40 = 0x40
      Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
    if @IndDesc <> '
    begin
      Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
      Select @Script = @Script + ' WITH ' + @IndDesc
    end
    /*
 **  Add the location of the data.
    */
  end
  if (@strPri_Key = ')
    Insert into #spscript values(@Script,0)
  else
    update #spscript set Script = Script + @strPri_Key where LastLine = 1
 
  Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index

Select Script from #spscript

set nocount off

return (0)

SQLSERVER6.5下的代码

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128)       /* The table to generate sql script */
as

declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID   TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length   TinyInt
declare @Prec     TinyInt
declare @Scale    TinyInt
declare @Status   TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID     SmallInt
declare @IndStatus SmallInt
declare @Index_Key varchar(255)
declare @Segment   SmallInt
declare @DBName    varchar(30)
declare @strPri_Key varchar (255)

/*
**  Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
  select @DBName = db_name()
    raiserror(15009,-1,-1,@ObjName,@DBName)
    return (1)
end

create table #spscript
(
    id     int IDENTITY not null,
    Script Varchar(255) NOT NULL,
    LastLine tinyint
)

declare Cursor_Column INSENSITIVE CURSOR
  for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
        case a.cdefault when 0 then ' ' else (select case c.text when "(' ')" then "(')" else c.text end
        from syscomments c where a.cdefault = c.id) end const_key
        from syscolumns a, systypes b where object_name(a.id) = @ObjName
        and a.usertype = b.usertype order by a.ColID

set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)

/* Get column information */
open Cursor_Column

fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key

Select @Script = '
while (@@FETCH_STATUS <> -1)
begin
  if (@@FETCH_STATUS <> -2)
  begin
    Select @Script = @ColName + ' ' + @TypeName
    if @UserType in (1,2,3,4)
      Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
    else if @UserType in (24)
      Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
                      + Convert(char(3),@Scale) + ') '
    else
      Select @Script = @Script + ' '
    if ( @Status & 0x80 ) > 0
      Select @Script = @Script + ' IDENTITY(1,1) '

    if ( @Status & 0x08 ) > 0
      Select @Script = @Script + ' NULL '
    else
      Select @Script = @Script + ' NOT NULL '
    if @cDefault > 0
      Select @Script = @Script + ' DEFAULT ' + @Const_Key
  end
  fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key
  if @@FETCH_STATUS = 0
  begin
    Select @Script = @Script + ','
    Insert into #spscript values(@Script,0)
  end
  else
  begin
    Insert into #spscript values(@Script,1)
    Insert into #spscript values(')',0)
  end
end
Close Cursor_Column
Deallocate Cursor_Column

/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
  for Select name,IndID,status,Segment from sysindexes where object_name(id)=@ObjName
              and IndID > 0 and IndID<>255 order by IndID
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment
while (@@FETCH_STATUS <> -1)
begin
  if @@FETCH_STATUS <> -2
  begin

    declare @i TinyInt
    declare @thiskey varchar(50)
    declare @IndDesc varchar(68) /* string to build up index desc in */

    Select  @i = 1
    while (@i <= 16)
    begin
      select @thiskey = index_col(@ObjName, @IndID, @i)
      if @thiskey is null
        break

      if @i = 1
        select @Index_Key = index_col(@ObjName, @IndID, @i)
      else
        select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
      select @i = @i + 1
    end
    if (@IndStatus & 0x02) > 0
      Select @Script = 'Create unique '
    else
      Select @Script = 'Create '
    if @IndID = 1
      select @Script = @Script + ' clustered '


    if (@IndStatus & 0x800) > 0
     select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
    else
     select @strPri_Key = '
     
    if @IndID > 1
      select @Script = @Script + ' nonclustered '
    Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
           + '(' + @Index_Key + ')'
    Select @IndDesc = '
    /*
 **  See if the index is ignore_dupkey (0x01).
    */
    if @IndStatus & 0x01 = 0x01
      Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
    /*
     **  See if the index is ignore_dup_row (0x04).
    */
    if @IndStatus & 0x04 = 0x04
      Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ','
    /*
 **  See if the index is allow_dup_row (0x40).
    */
    if @IndStatus & 0x40 = 0x40
      Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
    if @IndDesc <> '
    begin
      Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
      Select @Script = @Script + ' WITH ' + @IndDesc
    end
    /*
 **  Add the location of the data.
    */
    if @Segment <> 1
      select @Script = @Script + ' ON ' + name
  from syssegments
  where segment = @Segment
  end
  if (@strPri_Key = ')
    Insert into #spscript values(@Script,0)
  else
    update #spscript set Script = Script + @strPri_Key where LastLine = 1
 
  Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment
end
Close Cursor_Index
Deallocate Cursor_Index

Select Script from #spscript order by id

set nocount off

return (0)

SQL Server创建表语句介绍

原文地址:http://database.51cto.com/art/201010/231018.htm SQL Server创建表是最常见也是最常用的操作之一,下面就为您介绍SQL Serv...
  • kepa520
  • kepa520
  • 2017年12月13日 16:35
  • 147

sqlserver 创建分区表

我们知道很多事情都存在一个分治的思想,同样的道理我们也可以用到数据表上,当一个表很大很大的时候,我们就会想到将表拆 分成很多小表,查询的时候就到各个小表去查,最后进行汇总返回给调用方来加速我们的查询...
  • stargc
  • stargc
  • 2016年09月18日 10:39
  • 946

SQL SERVER 生成表结构的语句

通过表名生成表结构
  • t134679
  • t134679
  • 2015年09月01日 15:44
  • 805

MS SQLServer表数据生成Insert语句

数据库数据生成insert(MSSQL版),可将表中的数据生成insert或者update的sql脚本。比如您维护两个数据库,其中一个数据库中增加的数据也希望能够在另外一个数据中进行执行。...
  • swordmanli
  • swordmanli
  • 2016年09月17日 12:34
  • 286

SQLServe_使用T-SQL语句创建数据库、创建表以及表的约束

if exists(select * from sysdatabases where name='school') begin drop database school end go create...
  • u010996565
  • u010996565
  • 2017年01月21日 09:51
  • 1328

SQL语句(创建书库,创建建表,写入注释,获取注释)

--创建前先查询是否存在 if exists (select * from sys.databases where name="Users(数据库名字)")   drop database User...
  • u013010416
  • u013010416
  • 2013年12月10日 19:18
  • 3485

在SQL Server中用代码创建数据库、 创建表、创建主外键等SQL Server2008

在SQL Server中用代码创建数据库SQL Server2008 1.创建数据库  学生信息数据库创建实验一.sql CREATE database 学生信息_实验一 on ( /*-...
  • Liuchuang_MFC
  • Liuchuang_MFC
  • 2015年10月29日 22:48
  • 10865

【VBA】EXCEL通过VBA生成SQL,自动生成创建表结构SQL

编程需要与数据库很大,一个项目往往有很多的表,很多时候通过excel来维护表结构,将表结构整理到excel已经是件很累的事了,如果整理好的表结构只是用来看字段那就太浪费了,excel提供vba编程,我...
  • you_xian
  • you_xian
  • 2015年05月02日 19:04
  • 3791

SQL Server 2008中的分区表(二):如何添加、查询、修改分区表中的数据

在创建完分区表后,可以向分区表中直接插入数据,而不用去管它这些数据放在哪个物理上的数据表中。接上篇文章,我们在创建好的分区表中插入几条数据:       从以上代码中可以看出,我们一共在数...
  • luoyanqing119
  • luoyanqing119
  • 2014年02月15日 14:14
  • 9661

mysql sql语句创建表

当你用sql语句创建表的时候,例如: create table `tb_article` ( `a_id` int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY...
  • dongdekun369
  • dongdekun369
  • 2015年12月29日 09:55
  • 3697
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MS SQLSERVER 中如何得到表的创建语句
举报原因:
原因补充:

(最多只允许输入30个字)