CODEWITHSUNDEEP
sqlsql-server-2008
Skylude
Skylude

Reputation: 504

Finding stored procedure in SQL Server 2008 Management Studio

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,

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?

Thanks for any help on this.

Upvotes: 2

Views: 51663

Answers (7)

Quang Dinh Nhat
Quang Dinh Nhat

Reputation: 1

try this to view a stored procedure's code::

sp_helptext name_of_your_sp

Upvotes: 0

Marcello Miorelli
Marcello Miorelli

Reputation: 3698

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

Upvotes: 0

Saurabh Jain
Saurabh Jain

Reputation: 1712

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.

Now just in case if you wish to move your SP to a desired schema you need to modify your SP

alter procedure [schemaName].[USP_SP]

Upvotes: 0

Wojciech Szymanski
Wojciech Szymanski

Reputation: 358

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/

Upvotes: 0

Etch
Etch

Reputation: 3054

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.

Upvotes: 0

competent_tech
competent_tech

Reputation: 44971

You can always see the content of the stored procedure using sp_helptext:

sp_helptext 'mystoredprocname'

You can also find the stored procedures by

1) Selecting your database and expanding it

2) Expanding the Programmability node

3) Expanding the Stored Procedures node

4) Searching through the list of stored procedures until you find the one you are looking for

Upvotes: 7

Joe Stefanelli
Joe Stefanelli

Reputation: 135888

It does sound like a permission issue. Does this return anything for you?

use YourDatabaseName;
GO

sp_helptext 'YourSchemaName.YourProcedureName';

Upvotes: 4

Related Questions