| 在SQL Server中显示表结构的脚本片段 |
|
| 日期：2008-2-27 10:28:28 人气：0
[大
中 小] |
|
在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
|
|
| 出处：78软件收集整理 作者：不详 |
|
|
