SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.


SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'



this query will get all tables referenced in a store procedure.

SQL SERVER – Stored Procedure to display code (text) of Stored

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

From this you can view Stored Procedure to display by text (code)

Syntax
sp_helptext @objname = 'sp_name'


sp_helptext @objname = 'sp_viewemp'

SQL Server 2000/2005 show all tables in a Database

Freom this Query you can see all the Tables in Database


Select table_name FROM Information_Schema.Tables

SQL Server List all the Stored Procedures of a Database

List all the Stored Procedures of Database and their Definitions
using T-SQL in SQL Server 2005/2008 | Sql Server
From this query you can view all Stored Procedures in DataBase.
-------------------------------------------------------------------------------
SELECT obj.Name as SPName,
modu.definition as SPDefinition,
obj.create_date as SPCreationDate
FROM sys.sql_modules modu
INNER JOIN sys.objects obj
ON modu.object_id = obj.object_id

WHERE obj.Name = 'stp_tblname'
----------------------------------------------------------------