Finding Dynamic SQL's Dependencies

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

  • 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.

  • 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

  • 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.

  • 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:

  • Thanks for the script.

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

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