March 16, 2015 at 9:26 am
I am rewriting several stored procedures that originally had lots of "multiplicated" code. I am aware that references to objects within dynamic SQL do not create dependencies, so I intend to add code that will generate the dependencies but will NOT produce any output in both the Results and Messages tabs, not be overly "messy" or complicated, and have the least impact on execution plan creation as possible.
As we use a dependency list of tables used to help our support staff pinpoint possible data issues associated with each of these stored procedures, I'm mostly interested in tables, but would certainly prefer to include other object types as well.
I have tried a few methods already, including this:
SET @sql = N'SELECT Column1,Column2 FROM dbo.TableName';
...
/***************************************************************************************/
/* This code block is only to establish dependency of objects used within dynamic SQL. */
/* */
/* SET statements are used so that no output is produced in Results or Messages tabs. */
/* Object existence check avoids error 208, "Invalid object name" message. */
/***************************************************************************************/
DECLARE @DependentObject SQL_VARIANT;
IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL
SET @DependentObject = ( SELECT TOP (1) Column1,Column2 FROM dbo.TableName);
/* End code for dependency of objects used within dynamic SQL */
Not a big deal, but (of course) this code generates a SCAN execution plan step. I'm curious if anyone (or maybe everyone?) out there has designed a solution for this that is easier / faster / better, etc.?
Thanks,
~ Jeff
March 16, 2015 at 12:42 pm
Will this work for you?
declare @Proc sysname = 'uspGetEmployeeManagers'
;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 o.xtype = 'P' and o.name = @Proc)
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name
March 17, 2015 at 10:04 am
I appreciate the reply, but it seems to be directed at how to retrieve dependencies rather than force them to be created.
January 29, 2016 at 8:38 am
DECLARE @DependentObject SQL_VARIANT;
IF 1=2
BEGIN
IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL
SET @DependentObject = ( SELECT TOP (1) Column1,Column2 FROM dbo.TableName);
END
/* End code for dependency of objects used within dynamic SQL */
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply