在SQL Server中显示表结构的脚本片段

在SQL Server中显示表结构的脚本片段：

比如现实表TEST1的结构就run sp_showtable 'TEST1'IF OBJECT_ID('dbo.sp_showtable') IS NOT NULLBEGINDROP PROCEDURE dbo.sp_showtableIF OBJECT_ID('dbo.sp_showtable') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_showtable >>>'ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_showtable >>>'ENDgoSET ANSI_NULLS ONgoSET QUOTED_IDENTIFIER ONgo

create procedure [dbo].[sp_showtable] @tablename varchar(50)as beginselect '**************************************'print @tablename +' Structure is 'select b.name as ColumnName, case when c.name in ( 'nvarchar','char','nchar','varchar') then c.name+'('+convert(varchar(4),b.prec)+')'when c.name in ('decimal','numeric','float')then c.name+'('+convert(varchar(4),b.prec)+','+convert(varchar(4),b.scale)+')'when c.name in ('text','tinyint','image','int','smalldatetime','datetime','bigint','timestamp','money') then c.nameelse '?????????'end as Type,case b.isnullable when 0 then 'not null' else 'null' end as 'Null' from sysobjects a ,syscolumns b, systypes cwhere a.name=@tablename and a.id=b.idand b.usertype=c.usertypeand b.xusertype=c.xusertypeorder by b.colorder

end

goSET ANSI_NULLS OFFgoSET QUOTED_IDENTIFIER OFFgoIF OBJECT_ID('dbo.sp_showtable') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_showtable >>>'ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_showtable >>>'go

IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULLBEGINDROP PROCEDURE dbo.sp_showtable_insertIF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_showtable_insert >>>'ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_showtable_insert >>>'ENDgoSET ANSI_NULLS ONgoSET QUOTED_IDENTIFIER ONgo