什么查询可以返回SQL Server数据库中所有存储过程的名称
如果查询可以排除系统存储过程,那将更有帮助。
什么查询可以返回SQL Server数据库中所有存储过程的名称
如果查询可以排除系统存储过程,那将更有帮助。
当前回答
USE DBNAME
select ROUTINE_NAME from information_schema.routines
where routine_type = 'PROCEDURE'
GO
这将在mssql上工作。
其他回答
正如Mike所说,最好的方法是使用information_schema。只要不在主数据库中,系统存储过程就不会返回。
SELECT *
FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
如果出于某种原因,主数据库中有非系统存储过程,可以使用查询(这会过滤掉大多数系统存储过程):
SELECT *
FROM [master].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')
这也可以帮助列出除了系统过程之外的过程:
select * from sys.all_objects where type='p' and is_ms_shipped=0
select * from DatabaseName.INFORMATION_SCHEMA.ROUTINES where routine_type = 'PROCEDURE'
select * from DatabaseName.INFORMATION_SCHEMA.ROUTINES where routine_type ='procedure' and left(ROUTINE_NAME,3) not in('sp_', 'xp_', 'ms_')
SELECT name, type FROM dbo.sysobjects
WHERE (type = 'P')
这将显示所有的存储过程和代码:
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'
这个,列出所有你想要的东西
在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