Menu
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.
Where does SQL Server store the procedure's code?
607
Stored procedure in SQLTemporary procedures are stored in tempdb, and there are two types of temporary procedures: local and global. Local procedures are only visible to the current user connection, while global procedures are visible to any user after they are created.
A stored procedure (sp) is a group of SQL requests, saved into a database. In SSMS, they can be found just near the tables. Actually in terms of software architecture, it's better to stored the T-SQL language into the database, because if a tier changes there would be no need to modify another.
First, run SQL Server Management Studio and connect to the Database Engine. Next, under Object Explorer, expand the database in which you have created a procedure, and then expand “Programmability” option. Next, expand “Stored Procedures”, right-click the procedure you want and then select “View Dependencies” option.
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') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
