在MSSQL SERVER的相关数据处理中，我们可能会遇到需要查看或者提取出数据库中已创建表的创建SQL语句，这样能实现查看该表的创建过程以及实现表结构的复制移植等功能。然而在SQL Server数据库中，笔者发现没有什么很好的方式能够实现该需求操作，经过一段时间的分析和参考一些网络资料，逐渐能实现该需求的相关方法总结如下，以供大家参考使用。

create procedure SP_GET_TABLE_INFO

@ObjName varchar(128)

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)

 

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)

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

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

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)

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 = '' 

if @IndStatus & 0x01 = 0x01

Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','

 

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

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

Deallocate Cursor_Index

Select Script from #spscript

set nocount off

return (0)