在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