Where does SQL Server store the stored procedure code?

I once needed the lines of the stored procedures to be able to trace whether I have a reference to some function, procedure or table, or sometimes to try to find something inside of the sp's code开发者_JAVA百科.

Where does SQL Server store the procedure's code?

Use sys.sql_modules because definition is nvarchar(max) because it will not truncate long code.

In INFORMATION_SCHEMA.ROUTINES the ROUTINE_DEFINITION column is only nvarchar(4000) so if you try view the text of a long procedure and you will see that it is truncated.

Use this to search for text in any procedure, view, function:

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY o.type_desc,o.name 

use this to view the text of a given procedure, view, function:

select * from sys.sql_modules where object_id=object_id('YourProcedure')

You can use

select object_definition(object_id(routine_name)) from information_schema.routines

or

select object_definition(object_id) from sys.objects where name = 'foo'

or even

select object_definition(object_id('foo')); -- 'foo' is the table name

These versions are never truncated.

It stored it inside the system schema tables:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

See MSDN about the INFORMATION_SCHEMA.ROUTINES view:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/c75561b2-c9a1-48a1-9afa-a5896b6454cf.htm

For a content search on this, you can do the follwing:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search_string%'

If you are just trying to view the stored procedures code you go into the progammabiltity folder within your DB and they should be all stored in there under stored procedures.

If you are trying to search for references to other objects, then you can run a query like this:

SELECT * FROM syscomments WHERE TEXT LIKE '%searchstring%'

This will return any objects in the database that reference the search string. You can then look at these objects to see what stored procedures (and views and functions) are doing so.

View Dependencies

In SQL Server Management Studio, right-click on a table, and choose "View Dependencies". You will see every object that references the table

INFORMATION_SCHEMA

The actual code for a stored proc, view, constraint, etc is stored in SysComments. You should query this using the views provided in the schema Information_Schema. Here are all the components of the Information_Schema.

If you use SQL Server Management Studion, you can right click on the database you want, then click "Tasks -> Generate Scripts".

There you can generate a script with all the SP's in one single file, separated files, or directly to a query window, and search/change what you want.

Hope this helps.

(this is for SQL Server 2008, but i think 2005 has this functionality too)

EDIT:

You can also see one single SP code, by following this path "YourDB -> Programmability -> Stored Procedures", then right click on the SP you want to see, and click "Modify", and a query window is opened with the code.