Asked by:
Find the tables used in a SP

-
Example:
Create proc SQL
AS
BeginCREATE TABLE #temp
(
Name varchar(14),
Num varchar(9),
SSN varchar(9),
req int,
VN varchar(12),
PayrunDt smalldatetime,
UpdateDt smalldatetime
)
INSERT #tempSELECT DISTINCT
a.Name ,
a.Num ,
a.SSN,
a.req ,
c.VN ,
b.PayRunDt,
d.UpdateDt
FROM
dbo.Trv AS a,
dbo.Ver AS b,
dbo.VTrv AS c,
dbo.TrvVs AS d
where a.id=b.id and b.id=c.id and c.id=d.id
Select * from #temp
END
Question :I have X numbers of SP as shown in the above example(with different select queries loading data to x number of temp tables). i have to find the table and columns involved in the SP. can you please help me with the query? so that i can use it to for other SP's?
tried using the below Q, no luck
SELECT DISTINCT
SP_Name = O.name,
Table_Name = OO.name
FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON
O.id = D.id
INNER JOIN sys.sysobjects OO ON
OO.id = D.depid
WHERE O.xtype = 'P' and o.name in ('sp names')- Edited by Kalman TothModerator Tuesday, July 08, 2014 10:16 PM Spelling
Question
All replies
-
Object dependency varies from version to version.
Here is SQL Server 2008 object dependency discussion with examples:
http://www.sqlusa.com/bestpractices2008/object-dependency/
Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
-
I used the below script, almost returns what I am looking for but not the column name used in the proc, can you please help me on that?
create PROCEDURE [dbo].[get_crossdatabase_dependencies] AS
SET NOCOUNT ON;
CREATE TABLE #databases(
database_id int,
database_name sysname
);-- ignore systems databases
INSERT INTO #databases(database_id, database_name)
SELECT database_id, name FROM sys.databases
WHERE database_id > 4;
DECLARE
@database_id int,
@database_name sysname,
@sql varchar(max);CREATE TABLE #dependencies(
referencing_database varchar(max),
referencing_schema varchar(max),
referencing_object_name varchar(max),
referenced_server varchar(max),
referenced_database varchar(max),
referenced_schema varchar(max),
referenced_object_name varchar(max)
);WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
SELECT TOP 1 @database_id = database_id,
@database_name = database_name
FROM #databases;
SET @sql = 'INSERT INTO #dependencies select
DB_NAME(' + convert(varchar,@database_id) + '),
OBJECT_SCHEMA_NAME(referencing_id,'
+ convert(varchar,@database_id) +'),
OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
referenced_server_name,
ISNULL(referenced_database_name, db_name('
+ convert(varchar,@database_id) + ')),
referenced_schema_name,
referenced_entity_name
FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';
EXEC(@sql);
DELETE FROM #databases WHERE database_id = @database_id;
END; -
Why don't you try built-in stored proc
sp_depends <StoredprocedureName>
SELECT referenced_entity_name,referenced_minor_name,*FROM sys.dm_sql_referenced_entities ('<ProcName>', 'OBJECT');
--Prashanth
- Edited by Prashanth Jayaram Wednesday, July 09, 2014 3:28 PM
-
-
-