I thought you had to go to the sys.syscomments table to do this:
SELECT * FROM sys.procedures
WHERE [name] Like ‘GetAllElephants’
ORDER BY [name]
The above would return the stored procedure with the name ‘GetAllElephants’. one of the columns will contain the ID (object_id column) which you then pass to the OBJECT_DEFINTION function as shown below!
This will then return all of the contents of the stored procedure … that is the actual code that IS the stored procedure.
Or you could just do this:
You can do this with
- Stored Procedures
- Table Valued Functions
- Scarlar Valued functions
- Inline table Valued Functions
- and few other constraints and replication related items.. (see documentation below)