_

在sqlserver中显示表的结构

比如现实表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