Finding Dynamic SQL's Dependencies

Question

Finding Dynamic SQL's Dependencies

arthur.gimpel

SSC Enthusiast

Points: 104
More actions
October 13, 2012 at 3:07 pm

#393384

Comments posted to this topic are about the item Finding Dynamic SQL's Dependencies

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

The posted code wasn't formatted very well so wasn't able to read it without significant editting and it wouldn't run either (I only tried selected snippets from it ot verify posting problems). Seemed like a possibly interesting post, but the posting problems makes for a tough time trying to read the posted code.

arthur.gimpel SSC Enthusiast Points: 104 More actions · Answer 2

patrickmcginnis59 (10/15/2012)
The posted code wasn't formatted very well so wasn't able to read it without significant editting and it wouldn't run either (I only tried selected snippets from it ot verify posting problems). Seemed like a possibly interesting post, but the posting problems makes for a tough time trying to read the posted code.

Unfortunately the formatting somehow got wrong. Is there a way to re-post it?

Which part did you try to execute and did not work?

Here's the original formatted code:

SET NOCOUNT ON ;

DECLARE @ID INT,

@server NVARCHAR(100) ,

@DB NVARCHAR(100) ,

@Command NVARCHAR(4000) ,

@sql NVARCHAR(4000);

DECLARE @DependenciesTable TABLE

(

RowID INT ,

ServerName NVARCHAR(100) ,

DatabaseName NVARCHAR(100) ,

ObjectName NVARCHAR(100)

);

DECLARE C CURSOR

FOR

SELECT ID,

ServerName ,

DBName ,

SQLCommand

FROM dbo.CommandList

OPEN C

FETCH NEXT FROM C INTO @ID,@Server, @DB, @Command

WHILE @@FETCH_STATUS = 0

BEGIN

--Handle string operations (if exist) within the SQL code

SET @Command = REPLACE(@Command, '''', '''''''''')

--Drop the previous procedure in case it still exists (something got wrong during the previous run)

EXEC('EXEC( '' USE [' + @DB + '] ; IF OBJECT_ID(''''tmpProc'''') IS NOT NULL DROP PROCEDURE tmpProc;'') AT ' + @server +'' );

--Create the temporary procedcure

EXEC('EXEC( '' USE [' + @DB + '] ; EXEC (''''CREATE PROCEDURE tmpProc AS BEGIN ' + @Command + ' END'''')'') AT ' + @server +'');

--get dependencies

INSERT INTO @DependenciesTable

EXEC( 'EXEC ('' USE [' + @DB + '] ;SELECT ' + @ID + ',ISNULL(referenced_server_name,''''' + @server + ''''') AS [ServerName],ISNULL(referenced_database_name, ''''' + @DB + ''''') AS [DatabaseName],referenced_schema_name AS SchemaName,referenced_entity_name AS ObjectName FROM sys.dm_sql_referenced_entities(''''dbo.tmpProc'''',''''OBJECT'''')'') AT ['+ @server +'] ');

--Drop the temporary procedure

EXEC('EXEC( '' USE [' + @DB + '] ; IF OBJECT_ID(''''tmpProc'''') IS NOT NULL DROP PROCEDURE tmpProc;'' ) AT [' + @server +']')

FETCH NEXT FROM C INTO @ID,@Server, @DB, @Command

END

CLOSE C

DEALLOCATE C

SELECT DISTINCT *

FROM @DependenciesTable

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 3

arthur.gimpel (10/15/2012)
patrickmcginnis59 (10/15/2012)
The posted code wasn't formatted very well so wasn't able to read it without significant editting and it wouldn't run either (I only tried selected snippets from it ot verify posting problems). Seemed like a possibly interesting post, but the posting problems makes for a tough time trying to read the posted code.
Unfortunately the formatting somehow got wrong. Is there a way to re-post it?
Which part did you try to execute and did not work?

Portions that needed line breaks were jammed together without spaces and subsequently were unuseable. Thanks for reposting the code, I often find interesting stuff in these posts. You might consider emailing a site administrator for some help in reformatting the original post.

arthur.gimpel SSC Enthusiast Points: 104 More actions · Answer 4

patrickmcginnis59 (10/15/2012)
arthur.gimpel (10/15/2012)
patrickmcginnis59 (10/15/2012)
The posted code wasn't formatted very well so wasn't able to read it without significant editting and it wouldn't run either (I only tried selected snippets from it ot verify posting problems). Seemed like a possibly interesting post, but the posting problems makes for a tough time trying to read the posted code.
Unfortunately the formatting somehow got wrong. Is there a way to re-post it?
Which part did you try to execute and did not work?
Portions that needed line breaks were jammed together without spaces and subsequently were unuseable. Thanks for reposting the code, I often find interesting stuff in these posts. You might consider emailing a site administrator for some help in reformatting the original post.

That is exactly what I'm going to do. Thanks:cool:

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 9, 2016 at 1:15 pm

#1877574

Thanks for the script.