什么查询可以返回SQL Server数据库中所有存储过程的名称
如果查询可以排除系统存储过程,那将更有帮助。
什么查询可以返回SQL Server数据库中所有存储过程的名称
如果查询可以排除系统存储过程,那将更有帮助。
当前回答
你可以尝试这个查询来获取存储过程和函数:
SELECT name, type
FROM dbo.sysobjects
WHERE type IN (
'P', -- stored procedures
'FN', -- scalar functions
'IF', -- inline table-valued functions
'TF' -- table-valued functions
)
ORDER BY type, name
其他回答
这将显示所有的存储过程和代码:
select sch.name As [Schema], obj.name AS [Stored Procedure], code.definition AS [Code] from sys.objects as obj
join sys.sql_modules as code on code.object_id = obj.object_id
join sys.schemas as sch on sch.schema_id = obj.schema_id
where obj.type = 'P'
根据我的理解,“首选”方法是使用information_schema表:
select *
from information_schema.routines
where routine_type = 'PROCEDURE'
SELECT name,
type
FROM dbo.sysobjects
WHERE (type = 'P')
这个,列出所有你想要的东西
在Sql Server 2005, 2008, 2012:
Use [YourDataBase]
EXEC sp_tables @table_type = "'PROCEDURE'"
EXEC sp_tables @table_type = "'TABLE'"
EXEC sp_tables @table_type = "'VIEW'"
OR
SELECT * FROM information_schema.tables
SELECT * FROM information_schema.VIEWS
这将返回所有sp名称
Select *
FROM sys.procedures where [type] = 'P'
AND is_ms_shipped = 0
AND [name] not like 'sp[_]%diagram%'