SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Insertion code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mercyjhansi
Starting Member

3 Posts

Posted - 02/19/2014 :  01:44:01  Show Profile  Reply with Quote
Is there a query to find in which all sps a particular table is inserted?

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/19/2014 :  07:01:24  Show Profile  Reply with Quote


;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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/19/2014 :  07:02:01  Show Profile  Reply with Quote
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 - 02/21/2014 :  05:22:58  Show Profile  Reply with Quote
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.

Edited by - mercyjhansi on 02/26/2014 06:43:58
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000