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

MS SQLSERVER中如何得到表的创建语句2009-11-19肖波MS SQLSERVER只能得到存储过程的创建语句,方法如下:

sp_helptext procedureName

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

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

SQLSERVER2000/2005 下的代码

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）