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

比如现实表TEST1的结构就run sp_showtable "TEST1"
IF OBJECT_ID("dbo.sp_showtable") IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_showtable
IF OBJECT_ID("dbo.sp_showtable") IS NOT NULL
PRINT "<<< FAILED DROPPING PROCEDURE dbo.sp_showtable >>>"
ELSE
PRINT "<<< DROPPED PROCEDURE dbo.sp_showtable >>>"
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go

create procedure [dbo].[sp_showtable] @tablename varchar(50)
as 
begin
select "**************************************"
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.name
else "?????????"
end as Type,
case b.isnullable when 0 then "not 
null" else "null" end as "Null" 
from sysobjects a ,syscolumns b, systypes c
where a.name=@tablename 
and a.id=b.id
and b.usertype=c.usertype
and b.xusertype=c.xusertype
order by b.colorder 

end

go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID("dbo.sp_showtable") IS NOT NULL
PRINT "<<< CREATED PROCEDURE dbo.sp_showtable >>>"
ELSE
PRINT "<<< FAILED CREATING PROCEDURE dbo.sp_showtable >>>"
go

IF OBJECT_ID("dbo.sp_showtable_insert") IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_showtable_insert
IF OBJECT_ID("dbo.sp_showtable_insert") IS NOT NULL
PRINT "<<< FAILED DROPPING PROCEDURE dbo.sp_showtable_insert >>>"
ELSE
PRINT "<<< DROPPED PROCEDURE dbo.sp_showtable_insert >>>"
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go