How check that how many stored procedure will affect after deleting one table in SQL

Question

I am using SQL 2012.

I have large Database Structure in my project. I have around 10000 Stored Procedure in my Database.

I have to delete one table from the database, Is there any way that I can directly check that by deleteting my table how many stored procedure will affect.

Any help Will Be appriciated.

Answer 1

Using sp_depends:

EXEC sp_depends @objname = N'yourtablename'

Answer 2

If you are using SQL Server Management Studio, you can right click table and select "View Dependencies" to view the dependent objects

Answer 3

1) Procedures, Views, Functions, Triggers, FK's - view dependancies. View dependancies will give you all of the objects who referenced the table at the compile phase.

2) Procedures, Views, Functions, Triggers

select object_name(id),* 
from sys.syscomments 
where text like '%tableName%' 

Will give you names of objects where your table name appears. This is a string search. So if it appears in comments or dynamic sql, it will also be caught.

*** if you're using dynamic sql that receives the table name from an outside source, this is something you can only catch at the execution stage.

3) Jobs -

SELECT  *
FROM    msdb.dbo.sysjobs j
JOIN    msdb.dbo.sysjobsteps js
    ON  js.job_id = j.job_id 
WHERE   js.command LIKE N'%TableName%'

Will give you the names of the jobs where the table is found in the steps code. This is also a string matching search.

Answer 4

Import the database into a new SSDT project.

Then you can right click the object name and use "find all references".

You may also/instead want to search for the text string in all files if it might be referenced by dynamic SQL.