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)[email protected]
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)[email protected]
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)
