how to populate child dependecies for an object or any table type.
-
this is my query.It is showing all dependencies for particular table type.but i need the child tables type also using this query.It is showing through tabletype then right click view dependencies and it showing child items in tree
nodes.I am thinking that o its using depthfirst search algorithm(not sure about this).
may be it will possible using cte.
SELECT referencing_entity_name AS ObjectName,
case s.type when 'P' then 'storedprocedure' when 'V' then 'View' when 'FN' then 'function'
when 'U' then 'Table' when 'TT' then 'TableType' else '' end as ObjectType
FROM sys.dm_sql_referencing_entities ('dbo.tabletype, 'type')
join sys.objects s on referencing_id=s.object_id
If any one knows Solution please send reply soon.
Malleswarareddy
I.T.Analyst
MCITP(70-451) -
malleswarareddy_m (3/23/2011)
...i need the child tables type also using this query.It is showing through tabletype then right click view dependencies and it showing child items in tree
nodes.
...
It sounds like maybe a recursive CTE may be what you need to use however I am a little unclear as to what you are after. What do you mean by "i need the child tables type also using this query"? Perhaps if you provided a sample of your expected resultset it would help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Malleswarareddy
I.T.Analyst
MCITP(70-451) -
You guessed correct .I need a recursive cte but am getting dependencies using dynamic management function.
but it is not supporting to write the code for recursive ctes .
for example when we rightclick and click on view dependencies then it will show
a tree node with dependencies and also some had child objects.
so same i need using query.
Malleswarareddy
I.T.Analyst
MCITP(70-451) -
malleswarareddy_m (3/24/2011)
You guessed correct .I need a recursive cte but am getting dependencies using dynamic management function.but it is not supporting to write the code for recursive ctes .
for example when we rightclick and click on view dependencies then it will show
a tree node with dependencies and also some had child objects.
so same i need using query.
ok, if something else is doing it, like the GUI, then you can just set up profiler, runt he GUI event, and capture the sql(s) from profiler to recreate it yourself;
from there you can see if it is running a single pretty query, or re-querying for each node in it's first list.
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply