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 smallint
declare @Prec smallint
declare @Scale smallint
declare @Status smallint
declare @cDefault int
declare @DefaultID smallint
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)
SP_GET_TABLE_INFO 'student'