MS SQLSERVER 中如何得到表的创建语句,MS SQLSERVER 中如何得到表的创建语句
【 tulaoshi.com - SQLServer 】
MS SQLSERVER 只能得到存储过程的创建语句,方法如下:
sp_helptext procedureName
但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考.
该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句.
SQLSERVER2000 下的代码
(本文来源于图老师网站,更多请访问http://www.tulaoshi.com/sqlserver/)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 #spsc
来源:http://www.tulaoshi.com/n/20160129/1498245.html
看过《MS SQLSERVER 中如何得到表的创建语句》的人还看了以下文章 更多>>