Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Insertion code

Author  Topic 

mercyjhansi
Starting Member

3 Posts

Posted - 2014-02-19 : 01:44:01
Is there a query to find in which all sps a particular table is inserted?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-02-19 : 07:01:24


;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE oo.name = 'ProcedureName' and o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-02-19 : 07:02:01
Refer
http://sqlhints.com/2013/05/06/how-to-find-all-dependencies-of-a-table-in-sql-server/

--
Chandu
Go to Top of Page

mercyjhansi
Starting Member

3 Posts

Posted - 2014-02-21 : 05:22:58
bandi: Hey that query is returning all the sps in which the table is used. I need only the sps in which the table is inserted.
Go to Top of Page
   

- Advertisement -