1

How to get table names alone used in Stored Procedure by sp_depends. For example 

EXEC sp_depends Samplesp1

It gives table names and its column names used in samplesp1. But I need table names alone.

sqluser
  • 5,502
  • 7
  • 36
  • 50
Karthikeyan
  • 333
  • 5
  • 17

2 Answers2

2

You can save the result into a temp table

CREATE TABLE #tmp
(
    name VARCHAR(250)
    ,[type] VARCHAR(250)
    ,updated CHAR(2)
    ,selected CHAR(3)
    ,[column] VARCHAR(250)
)

INSERT #tmp 
EXEC sp_depends @objname = N'yoursp'

SELECT name FROM #tmp
sqluser
  • 5,502
  • 7
  • 36
  • 50
  • 1
    Plus one for answering while I was looking for the duplicate. – Rohit Vipin Mathews Jun 02 '15 at 06:40
  • 1
    `sp_depends` has two recordsets, so this solution results in _Msg 213, Level 16, State 7, Procedure sp_depends, Line 68 [Batch Start Line 0] Column name or number of supplied values does not match table definition._. Not sure if this is a new feature of this proc and also not sure if the `openquery` solution fixes it – Nick.Mc Oct 12 '20 at 03:06
2

The easiest method is creating a Temporary table like sqluser have mentioned in this answer.

CREATE TABLE #temp
 (NAME VARCHAR(250)
, [type] VARCHAR(250)
, updated CHAR(2)
, selected CHAR(3)
, [column] VARCHAR(250))

-- insert the data from procedure's rresult into temporary table
INSERT #temp
EXEC sp_depends @objname = N'Samplesp1'

SELECT NAME
FROM #temp t
--Filter condition
WHERE t.type = 'user table'

drop TABLE #temp

This article- How to Share Data between Stored Procedures shows various other methods suitable for your scenario.

Of all the options my personal favorite is using the OPENQUERY:

SELECT  *  FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue

There is an already well answered question in SO about the scenario - SQL Server - SELECT FROM stored procedure. Looking into the answers would give you some better options as well.

Community
  • 1
  • 1
Rohit Vipin Mathews
  • 11,629
  • 15
  • 57
  • 112