在 SQL Server 2008 Management Studio 中查找存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7972437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding stored procedure in SQL Server 2008 Management Studio
提问by Skylude
I'm new to Windows servers and I have a hopefully quick question. I am looking for a stored procedure in SQL Server 2008 Management Studio. I ran this query,
我是 Windows 服务器的新手，我有一个希望很快的问题。我正在寻找 SQL Server 2008 Management Studio 中的存储过程。我运行了这个查询，
SELECT name, type
FROM dbo.sysobjects
WHERE (type = 'P')
Which returns a list of stored procedures. The one I am looking for is in the list. Now I need to know how/where to find this. I am browsing the structure on the left side and I see several folders of "Stored Procedures" but I do not see the item I am looking for in any of the lists. Is there SQL to show me the stored procedure or do I need to open it in SQL Server Management Studio? Could it also be a permission issue with the user I am connecting with?
它返回存储过程列表。我正在寻找的那个在列表中。现在我需要知道如何/在哪里找到它。我正在浏览左侧的结构，我看到几个“存储过程”文件夹，但在任何列表中都没有看到我要查找的项目。是否有 SQL 向我显示存储过程，还是我需要在 SQL Server Management Studio 中打开它？是否也可能是我正在连接的用户的权限问题？
Thanks for any help on this.
感谢您对此的任何帮助。
回答by competent_tech
You can always see the content of the stored procedure using sp_helptext:
您始终可以使用 sp_helptext 查看存储过程的内容：
sp_helptext 'mystoredprocname'
You can also find the stored procedures by
您还可以通过以下方式找到存储过程
1) Selecting your database and expanding it
1) 选择您的数据库并展开它
2) Expanding the Programmability node
2) 扩展可编程性节点
3) Expanding the Stored Procedures node
3）展开存储过程节点
4) Searching through the list of stored procedures until you find the one you are looking for
4) 搜索存储过程列表，直到找到所需的存储过程
回答by Joe Stefanelli
It does sound like a permission issue. Does this return anything for you?
这听起来像是一个许可问题。这对你有回报吗？
use YourDatabaseName;
GO
sp_helptext 'YourSchemaName.YourProcedureName';
回答by Etch
I believe the query for this is:
我相信对此的查询是：
select *
from yourDB.information_schema.routines
where routine_type = 'PROCEDURE'
However if you do not see your DB its more then likely the user you are using doesn't have access.
但是，如果您没有看到您的数据库，那么您使用的用户可能没有访问权限。
回答by Wojciech Szymanski
I am using this very handy extension which allows you to search and quickly find SQL in SQL Server Management Studio. You can quickly search for SQL text within stored procedures, functions, views etc. As for my writing it is free tool, have a look and learn more about it: http://www.red-gate.com/products/sql-development/sql-search/
我正在使用这个非常方便的扩展，它允许您在 SQL Server Management Studio 中搜索和快速查找 SQL。您可以在存储过程、函数、视图等中快速搜索 SQL 文本。至于我编写的它是免费工具，请查看并了解更多信息：http: //www.red-gate.com/products/sql-开发/sql-search/
回答by Saurabh Jain
We often follow the syntax:
我们经常遵循以下语法：
create procedure procedureName
.........
If you did this your stored procedure will be created under the dbo schema of your database wherein you executed the create procedure script.
如果您这样做了，您的存储过程将在您执行创建过程脚本的数据库的 dbo 模式下创建。
Now just in case if you wish to move your SP to a desired schema you need to modify your SP
现在以防万一，如果您希望将 SP 移动到所需的架构，您需要修改您的 SP
alter procedure [schemaName].[USP_SP]
回答by Marcello Miorelli
I have a script for that. here it is:
我有一个脚本。这里是：
--=====================================================================================
-- looking at all databases to find a stored procedure named @spName
-- marcelo miorelli
-- 1-april-2014
--=====================================================================================
DECLARE @SQL NVARCHAR(max)
,@spName VARCHAR(100) = 'usp_sel_CAEval4_comments' -- THE PROCEDURE THAT I AM LOOKING FOR
SELECT @SQL = STUFF((
SELECT CHAR(10) + ' UNION ALL ' + CHAR(10) +
' SELECT ' + quotename(NAME, '''') + ' AS DB_NAME ' + CHAR(10) +
' , SCHEMA_NAME(s.schema_id) AS THE_SCHEMA ' + CHAR(10) +
' , s.name COLLATE Latin1_General_CI_AS AS THE_NAME ' + CHAR(10) +
' FROM ' + quotename(NAME) + '.sys.procedures s ' + CHAR(10) +
' WHERE s.name = @spName
AND s.[type] = ''P'''
FROM sys.databases
ORDER BY NAME
FOR XML PATH('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 11, '')
--PRINT @SQL
EXECUTE sp_executeSQL @SQL
,N'@spName varchar(100)'
,@spName
