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.
精彩评论