SQL SERVER查询对象属性存储过程
Create PROCEDURE SP_ObjInfo
( @ObjName varchar(40)
)
AS
SET NOCOUNT ON
Declare
@Xtype char(4)
SELECT @ObjName=rtrim(@ObjName)
SELECT @Xtype=Xtype From sysobjects Where Name=@ObjName
--==========================================表和视图
IF @Xtype IN('U','V','F')
SELECT (case when a.colorder=1 then d.name else '' end)名称,
(case when a.colorder=1 and d.xtype='U' Then '表' when a.colorder=1 and d.xtype='V' then '视图' else ''end) as 属性,
(case when a.colorder=1 then isnull(f.value,'') else '' end) 说明,a.colorder 序号,a.name 字段名称,(case when (SELECT count(*)FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND
(colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
(case when convert(bit, ColumnProperty(a.id, a.name, N'IsIdentity'))=1 then '√' else '' end)标识列,
(case when (ColumnProperty(a.id, a.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(d.name)) else null end)标识种子,
(case when (ColumnProperty(a.id, a.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_incr(d.name)) else null end)标识递增量,
h.text 公式,
isnull(e.text,'') 默认值,
cast(isnull(g.[value],'') as varchar(800))AS 字段说明
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
left join sysobjects d on a.id=d.id and d.name<>'dtproperties' and d.xtype IN ('U','V','F')
left join dbo.syscomments e
on e.id = a.cdefault and e.colid = 1
and not exists (select * from dbo.syscomments where id = a.cdefault and colid = 2)
left join sysproperties f on d.id=f.id and f.smallid=0 and f.name='MS_Description'
left join sysproperties g on a.colid=g.smallid AND a.id = g.id
left join syscomments h on h.id = a.id and h.number = a.colid
where d.name LIKE @ObjName
order by a.id,a.colorder
--==========================================存储过程、触发器和函数
IF @Xtype IN('P','TR','FN')
select o.name 名称,(case when o.xtype='P' Then '存储过程' when o.xtype='FN' Then '函数'when o.xtype='TR' Then '触发器' else ''end) as 属性,
c.text 描述
from dbo.syscomments c INNER JOIN dbo.sysobjects o ON o.id = c.id
WHERE o.xtype IN ('P','TR','FN') and o.name=@ObjName
order by c.number, c.colid option(robust plan)
SET NOCOUNT OFF
GO
