Home Forums SQL Server 7,2000 Performance Tuning Any Catalog view to find objects referred in other database in dynamic query RE: Any Catalog view to find objects referred in other database in dynamic query

  • August 2, 2013 at 12:04 pm

    #1638300

    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!