请问有没有办法获取sqlsever中一个数据库里所有存储过程或者函数的名字

[timsx123]

请问有没有办法获取sqlsever中一个数据库里所有存储过程或者函数的名字

如果想实现两个不同服务器上的数据库的存储过程和函数的同步，用什么办法好啊？

我是新手，请大家多多指教！

[dawugui]

3.SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sql

SELECT DISTINCT 

TOP 100 PERCENT o.xtype, 

CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN 

'主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' 

THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' 

END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间, 

c.text AS 声明语句 

FROM dbo.sysobjects o LEFT OUTER JOIN 

dbo.syscomments c ON o.id = c.id 

WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND 

(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) 

ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 

'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' 

WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' 

ELSE '存储过程' END DESC

• 打赏
• 举报

回复 赞

[free1879]

use
yourDB;
go
select [name] 
from sysobjects
where xtype in('p','FN')

• 打赏
• 举报

回复 赞

[-狙击手-]

select [name]
from sysobjects
where xtype in('p','FN')

• 打赏
• 举报

回复 赞

[dawugui]

sql server 2000自带库pubs的结果.
xtype 类型 对象名 创建时间 更改时间 声明语句
----- ------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK 主键 PK__jobs__117F9D94 2000-08-06 01:33:52.983 2000-08-06 01:33:52.983 NULL
PK 主键 PK_emp_id 2000-08-06 01:33:53.203 2000-08-06 01:33:53.203 NULL
PK 主键 UPK_storeid 2000-08-06 01:33:52.547 2000-08-06 01:33:52.547 NULL
PK 主键 UPKCL_auidind 2000-08-06 01:33:52.140 2000-08-06 01:33:52.140 NULL
PK 主键 UPKCL_pubind 2000-08-06 01:33:52.217 2000-08-06 01:33:52.217 NULL
PK 主键 UPKCL_pubinfo 2000-08-06 01:33:53.093 2000-08-06 01:33:53.093 NULL
PK 主键 UPKCL_sales 2000-08-06 01:33:52.653 2000-08-06 01:33:52.653 NULL
PK 主键 UPKCL_taind 2000-08-06 01:33:52.437 2000-08-06 01:33:52.437 NULL
PK 主键 UPKCL_titleidind 2000-08-06 01:33:52.327 2000-08-06 01:33:52.327 NULL
C 约束 CK__authors__au_id__77BFCB91 2000-08-06 01:33:52.140 2000-08-06 01:33:52.140 ([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
C 约束 CK__authors__zip__79A81403 2000-08-06 01:33:52.153 2000-08-06 01:33:52.153 ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
C 约束 CK__jobs__max_lvl__145C0A3F 2000-08-06 01:33:52.983 2000-08-06 01:33:52.983 ([max_lvl] <= 250)
C 约束 CK__jobs__min_lvl__1367E606 2000-08-06 01:33:52.983 2000-08-06 01:33:52.983 ([min_lvl] >= 10)
C 约束 CK__publisher__pub_i__7C8480AE 2000-08-06 01:33:52.217 2000-08-06 01:33:52.217 ([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id] = '0877' or ([pub_id] = '0736' or [pub_id] = '1389'))) or [pub_id] like '99[0-9][0-9]')
C 约束 CK_emp_id 2000-08-06 01:33:53.203 2000-08-06 01:33:53.203 ([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
F 外键 FK__discounts__stor___0F975522 2000-08-06 01:33:52.873 2000-08-06 01:33:52.873 NULL
F 外键 FK__employee__job_id__1BFD2C07 2000-08-06 01:33:53.203 2000-08-06 01:33:53.203 NULL
F 外键 FK__employee__pub_id__1ED998B2 2000-08-06 01:33:53.203 2000-08-06 01:33:53.203 NULL
F 外键 FK__pub_info__pub_id__173876EA 2000-08-06 01:33:53.093 2000-08-06 01:33:53.093 NULL
F 外键 FK__roysched__title___0DAF0CB0 2000-08-06 01:33:52.763 2000-08-06 01:33:52.763 NULL
F 外键 FK__sales__stor_id__0AD2A005 2000-08-06 01:33:52.653 2000-08-06 01:33:52.653 NULL
F 外键 FK__sales__title_id__0BC6C43E 2000-08-06 01:33:52.653 2000-08-06 01:33:52.653 NULL
F 外键 FK__titleauth__au_id__0519C6AF 2000-08-06 01:33:52.437 2000-08-06 01:33:52.437 NULL
F 外键 FK__titleauth__title__060DEAE8 2000-08-06 01:33:52.437 2000-08-06 01:33:52.437 NULL
F 外键 FK__titles__pub_id__014935CB 2000-08-06 01:33:52.403 2000-08-06 01:33:52.403 NULL
V 视图 titleview 2000-08-06 01:33:58.437 2000-08-06 01:33:58.437
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id

P 存储过程 byroyalty 2000-08-06 01:33:58.547 2000-08-06 01:33:58.547
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage

P 存储过程 reptq1 2000-08-06 01:33:58.763 2000-08-06 01:33:58.763
CREATE PROCEDURE reptq1 AS
select pub_id, title_id, price, pubdate
from titles
where price is NOT NULL
order by pub_id
COMPUTE avg(price) BY pub_id
COMPUTE avg(price)

P 存储过程 reptq2 2000-08-06 01:33:58.983 2000-08-06 01:33:58.983
CREATE PROCEDURE reptq2 AS
select type, pub_id, titles.title_id, au_ord,
Name = substring (au_lname, 1,15), ytd_sales
from titles, authors, titleauthor
where titles.title_id = titleauthor.title_id AND authors.au_id = titleauthor.au_id
AND pub_
P 存储过程 reptq3 2000-08-06 01:33:59.200 2000-08-06 01:33:59.200
CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select pub_id, type, title_id, price
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
order by pub_id, type
COMPUTE count(title_i
TR 触发器 employee_insupd 2000-08-06 01:33:53.310 2000-08-06 01:33:53.310
CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,

（所影响的行数为 31 行）

• 打赏
• 举报

回复 赞

[free1879]

备份->恢复->清空数据

• 打赏
• 举报

回复 赞

[wea1978]

高人啊....收藏了...

• 打赏
• 举报

回复 赞

[timsx123]

好强...得慢慢消化，先谢谢高人！

• 打赏
• 举报

回复 赞