Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Any Catalog view to find objects referred in other database in dynamic query Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:29 AM
Points: 47, Visits: 165
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
Post #1480290
Posted Friday, August 2, 2013 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480387
Posted Friday, August 2, 2013 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 12,864, Visits: 31,709
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480483
Posted Friday, August 2, 2013 12:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 12,864, Visits: 31,709
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 [TABLE] ,
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480552
Posted Friday, August 2, 2013 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:29 AM
Points: 47, Visits: 165
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
Post #1480588
Posted Friday, August 2, 2013 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:29 AM
Points: 47, Visits: 165
Sure i'll try your both solutions.

Thanks
Post #1480590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse