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
