Any Catalog view to find objects referred in other database in dynamic query

  • Hi,

    Is there any catalog view for finding reference objects in other database or same but is written in dynamic query in view/store procedure/function.

    I found some like syscomments,sys.all_sql_modules,sys.sql_modules but is there some catalog view like

    sys.all_expression_dependencies which does not show objects referred in dynamic queries.

    Please Suggest :-).

    ,Regards

    Harsimranjeet Singh

  • harsimranjeetsinghwasson (8/2/2013)


    Hi,

    Is there any catalog view for finding reference objects in other database or same but is written in dynamic query in view/store procedure/function.

    I found some like syscomments,sys.all_sql_modules,sys.sql_modules but is there some catalog view like

    sys.all_expression_dependencies which does not show objects referred in dynamic queries.

    Please Suggest :-).

    ,Regards

    Harsimranjeet Singh

    I am not quite sure what you are looking for. Are you looking for references in things like stored procs where the references are in dynamic sql within those procs? Can you please try to clarify what you are looking for?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • AFAIK there's no view that would capture references in dynamic queries DIRECTLY;

    you could look at cached execution plans, and parse the xml of the plan for server/database/schema/table; but that's only going to get you items with cache-able plan, and that are still in the current cache since the last restart.

    the plans?

    SELECT decp.query_plan

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp

    WHERE decp.query_plan IS NOT NULL

    at that point, i'd stick the xml in a text file and use Regulus expressions on it instead. playing with xml is a little obscure for me sometimes.

    i have this saved in my snippets for example, where i'm getting missing index schema/table from the plans:

    WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

    SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]', 'NVARCHAR(256)') AS DatabaseName

    ,s.sql_handle

    ,s.total_elapsed_time

    ,s.last_execution_time

    ,s.execution_count

    ,s.total_logical_writes

    ,s.total_logical_reads

    ,s.min_elapsed_time

    ,s.max_elapsed_time

    ,p.query_plan

    ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName

    ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact

    ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage

    ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p

    CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup') AS t1 (ColumnGroup)

    CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)

    WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1

    ORDER BY s.total_elapsed_time DESC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • this query specifically gives me the server/database/table/column that was used int eh output list of the query.

    maybe this gets you what you were looking for?

    i had to create a linked server adhoc query to get a value in Server column to show up int he query plan

    WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

    SELECT

    decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') ,

    decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Server)[1]' , 'varchar(100)') AS [Server] ,

    decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]' , 'varchar(100)') AS [DATABASE] ,

    decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]' , 'varchar(100)') AS

    ,

    decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]' , 'varchar(100)') AS [COLUMN] ,

    decp.query_plan

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp

    WHERE decp.query_plan IS NOT NULL

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Yes, i need the detail of objects referred from other database in a database

    for example : departmentdw database has some procedure that refer amazon database objects like tables

    USe departmentdw

    create proc a

    as

    begin

    exec 'select * from Amazom.dbo.people'

    end

    i want to find these database references which are mentioned in dynamic query. I can used other catalogs views

    like syscomments and all writter in my question by i like to know some catalog view like sys.all_expression_dependencies which i found out doesn't show database object referred in dynamic queries

  • Sure i'll try your both solutions.

    Thanks 🙂

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

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