If exists (select name from sysobjects where name='SP_TBSTRU' and xtype='P')
Drop procedure dbo.SP_TBSTRU
go
CREATE PROCEDURE SP_TBSTRU
@Dbname varchar(30) ='BHDB',
@Tbname varchar(30) ='ABANK'
AS
If not object_id('tempdb..#TEMP') is null
Drop table #TEMP
-- set @dbname=''
-- SET @TBNAME=''
SELECT
Dbname = @dbname,
Tbname = @Tbname,--case when a.colorder=1 then LEFT(d.name,30) else '' end,
Itm = 100,
Fldname = LEFT(a.name,30),
Fldkey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
Fldtype = LEFT(b.name,20),
Fldsize = a.length,
Fldlength = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
Flddec =a.scale,
Fldnull =a.isnullable,
Flddefault = CAST(isnull(e.text,'') as varchar(254)),
Tbexplain = CAST(case when a.colorder=1 then isnull(f.value,'') else '' end as varchar(254)),
Fldexplain = CAST(isnull(g.[value],'') as varchar(254))
INTO #TEMP
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where d.name=@TBname
SELECT * FROM #TEMP
drop table #temp
GO
--调用示例:
exec SP_TBSTRU @Dbname ='BHDB',@Tbname='ABACC'
--xtype 类型说明
xtype char(2) 对象类型。可以是下列对象类型中的一种:
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束(类型是 K)
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束(类型是 K)
V = 视图
X = 扩展存储过程