在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