MS SQL SERVER中如何得到表的创建语句
类别: 数据库教程 
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)
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)
