1、查询用户存储过程名:
SELECT name, owner = USER_NAME(uid), objtype = power(2,sysstat & 0xf) FROM sysobjects WHERE (POWER(2, sysstat & 0xf) & 253 <> 0) AND NOT ((OBJECTPROPERTY(id, N'IsDefaultCnst') = 1) AND (category & 0x0800 <> 0)) AND (OBJECTPROPERTY(id, N'IsMSShipped') <> 1 AND (name NOT LIKE N'#%')) and power(2,sysstat & 0xf) = 16 ORDER BY name, owner
2、根据存储过程名,通过结构同以下SQL语句依次获取用户存储过程脚本信息:
if exists (select * from dbo.syscomments where id=object_id(N'[dbo].[yyy]'))
select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype), datalength(c.text), convert(varbinary(8000), c.text), 0 from dbo.syscomments c, dbo.sysobjects o where o.id = c.id and c.id = object_id(N'[dbo].[yyy]') order by c.number, c.colid
其中dbo.yyy是存储过程名字。
3)列说明说明
EXECUTE sp_addextendedproperty N'MS_Description', '测试', N'user', N'dbo', N'table', N'temp1', N'column', N'deptid'
其中测试时要添加的说明字符,temp1是表名称,deptid
4)表说明
EXECUTE sp_addextendedproperty N'MS_Description', '测试', N'user', N'dbo', N'table', N'temp1', null, null
其中测试时要添加的说明字符,temp1是表名称。