Dependency Tools

All,
Looking for recomendations on a tool that can check to see
if a table is being used in that db, or by any other db on
the same server. I have several db's on the same server
and sometimes a proc in another db needs a particular
table to be there.
Thanks in Advance,
Gary Abbott
Hi Gary,
Why not try
AGS SQL Scribe Documentation Builder. It produces HTMLHelp based
documentation which can then be searched. This way you are guaranteed to
find all objects that use the table.
http://www.ag-software.com/ags_scribe_index.aspx
Kind regards
Greg Obleshchuk
Partner
A & G Software
http://www.ag-software.com
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:138001c4265a$b9e34d10$a101280a@.phx.gbl...
> All,
> Looking for recomendations on a tool that can check to see
> if a table is being used in that db, or by any other db on
> the same server. I have several db's on the same server
> and sometimes a proc in another db needs a particular
> table to be there.
> Thanks in Advance,
> Gary Abbott

Dependency Tools

All,
Looking for recomendations on a tool that can check to see
if a table is being used in that db, or by any other db on
the same server. I have several db's on the same server
and sometimes a proc in another db needs a particular
table to be there.
Thanks in Advance,
Gary AbbottHi Gary,
Why not try
AGS SQL Scribe Documentation Builder. It produces HTMLHelp based
documentation which can then be searched. This way you are guaranteed to
find all objects that use the table.
http://www.ag-software.com/ags_scribe_index.aspx
--
Kind regards
Greg Obleshchuk
Partner
A & G Software
http://www.ag-software.com
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:138001c4265a$b9e34d10$a101280a@.phx.gbl...
> All,
> Looking for recomendations on a tool that can check to see
> if a table is being used in that db, or by any other db on
> the same server. I have several db's on the same server
> and sometimes a proc in another db needs a particular
> table to be there.
> Thanks in Advance,
> Gary Abbott

Dependency Tools

All,
Looking for recomendations on a tool that can check to see
if a table is being used in that db, or by any other db on
the same server. I have several db's on the same server
and sometimes a proc in another db needs a particular
table to be there.
Thanks in Advance,
Gary AbbottHi Gary,
Why not try
AGS SQL Scribe Documentation Builder. It produces HTMLHelp based
documentation which can then be searched. This way you are guaranteed to
find all objects that use the table.
http://www.ag-software.com/ags_scribe_index.aspx
Kind regards
Greg Obleshchuk
Partner
A & G Software
http://www.ag-software.com
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:138001c4265a$b9e34d10$a101280a@.phx.gbl...
> All,
> Looking for recomendations on a tool that can check to see
> if a table is being used in that db, or by any other db on
> the same server. I have several db's on the same server
> and sometimes a proc in another db needs a particular
> table to be there.
> Thanks in Advance,
> Gary Abbott

dependency relations are different from different mining structures with same mining algorithms?

Hi, all here..

I got one problem in my data analysis case with SQL Server 2005 data mining algorithms-in the dependency network, the attributes mostly affected the predictive value are different in different mining structures tho.

For example, I got a, b,c as input attributes to predict attribute d, in mining structure 1 (with all attributes discretized), i got a as the attribute which has the strongest relation with predictive attribute d. However in mining structure 2 (with all attributes values continuous), I got attribute b as the strongest attribute with predictive attribute d?

So what is the problem tho? In this case, how can I tell which attribute actually has the strongest relation with predictive attribute?

Thanks a lot in advance for help.

This is expected. You are modeling two different problems here. In the discretized space you are asking "are ranges of data predictive of my target", in the other you are using the raw values.

For example, say you are predicting credit worthyness based on age and salary. If you discretize you are asking "which age and salary ranges indicate creditworthiness." Your ranges for age could be 0-10,11-20,21-40, etc. and salary could be 0-30k, 30-60k, etc.

In this model you may find that people who are in the 0-10 bucket have very little creditworthiness and therefore Age is the strongest predictor.

If you choose continuous, you may find that overall, a very low salary, e.g. < 13K was a stronger indicator and age overall played a minor role. This could be because the bucketing hid detail from the algorithm, or even (if predicting a continuous) because the salary played a stronger role in a regression formula.

|||Hi, Jamie,thanks a lot .

dependency problems

hi all;
i have a problem with a view which uses an sp . while appyling the
snapshot,
the agent complains about the sp that view uses. i used to think that
agent take care of dependencies but it is not as in that case.
i drop publication and add the view as the last article of the publication
to be sure that sp is added before the view.. but that does not work too.
now i think to create the sp in the subscriber before the snapshot is
applied.
but how does the agent determine the order of scripts to apply?
another problem is about referential integrities. i am using dynamic
snapshots. the tables that have foreign keys complains about the
constraints. how can i be sure that data that is referenced inserted first.
thnx so much...
A view uses a stored procedure - are you sure - I'd like to see how this is
done
I suspect the view depends on other views or tables, and these dependencies
are not reflected in sql server's list of dependencies. You could try
refreshing the view (sp_refreshview), then add it to the publication and see
if that helps. Alternatively you could replicate the view using
sp_addscriptexec.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uJwg0yjdFHA.2128@.TK2MSFTNGP15.phx.gbl...
> A view uses a stored procedure - are you sure - I'd like to see how this
is
> done
i am sorry , replication is a real nightmare and i am so sleepy.
, not an sp , it is a udf.

> I suspect the view depends on other views or tables, and these
dependencies
> are not reflected in sql server's list of dependencies. You could try
> refreshing the view (sp_refreshview), then add it to the publication and
see
> if that helps. Alternatively you could replicate the view using
> sp_addscriptexec.
i investigated sysdepends , but it only reports the tables that the view
depends on , not the udf . (this view is not depending on any other view
also)
i would use addscriptexec to create the udf at the subscriber, i did not
use it before, therefore i have some suspects. would the script run,
everytime i merge or only when the subscription is initialized?

> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sysdepends on your publisher is out of sync. Script out each object in your
database and use this as your pre snapshot command, or simply run this
script in your subscriber database before running your publication. Note
also the sp_addscriptexec only works for subscriptions deployed through
uncs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"uykusuz" <yb> wrote in message
news:OqEJfhjdFHA.1404@.TK2MSFTNGP09.phx.gbl...
> hi all;
> i have a problem with a view which uses an sp . while appyling the
> snapshot,
> the agent complains about the sp that view uses. i used to think that
> agent take care of dependencies but it is not as in that case.
> i drop publication and add the view as the last article of the
publication
> to be sure that sp is added before the view.. but that does not work too.
> now i think to create the sp in the subscriber before the snapshot is
> applied.
> but how does the agent determine the order of scripts to apply?
> another problem is about referential integrities. i am using dynamic
> snapshots. the tables that have foreign keys complains about the
> constraints. how can i be sure that data that is referenced inserted
first.
> thnx so much...
>
>
>
|||sp_addscriptexec only runs as a one off.
I'd consider placing the udfs in a separate publication and make sure this
runs before the publication containing the views.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thank you..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23iGwltldFHA.2688@.TK2MSFTNGP14.phx.gbl...
> sp_addscriptexec only runs as a one off.
> I'd consider placing the udfs in a separate publication and make sure this
> runs before the publication containing the views.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%232TTupldFHA.1288@.tk2msftngp13.phx.gbl...
> Sysdepends on your publisher is out of sync. Script out each object in
your
> database and use this as your pre snapshot command, or simply run this
> script in your subscriber database before running your publication.

> Note also the sp_addscriptexec only works for subscriptions deployed
>through uncs.
Hilary, i am using dynamic snapshots, therefore i am not deploying the
subscriptions through unc, does that mean i can not use sp_addscriptexec ?
in fact i am a bit confused about dynamic snapshots. can you explain what a
dynamic snapshot agent really does?
when i create a dynamic snapshot in a folder, as far as i understand ,
firstly necassary scripts are copied to that folder then bcp's are created
according to filters. i had some strange errors about the files that have
different names in normal and dynamic snapshot folders. for example one is
named myUdf_2012 other is myUdf_2006. When i copy myUdf_2012 from normal
snapshot folder to dynamic snapshot folder , it worked.
[vbcol=seagreen]
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "uykusuz" <yb> wrote in message
> news:OqEJfhjdFHA.1404@.TK2MSFTNGP09.phx.gbl...
> publication
too.
> first.
>

Dependency network question!!

Hi, I have a question concerning microsoft data mining in sql server 05.

The thing is: when i create a new AS project in my visual studio define data source view and create model, let it be decition tree, i can see "dependency network" where i can figure how one column relate to another.

I want to use this knowledge in my C# application in order to choose right column to my next task. How do i get the result using dmx or amo? Do you have any idea?

Run SQL Server Profiler when opening dependancy network tab of the viewer. You will see what store procedure the viewer calls to display the dependency network. Then call the store procedure from your C# application.

Here is what you get back from the store procedure. (I executed

CALL System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('v Target Mail', 60) )

NODE_TYPE NODE_UNIQUE_NAME_1 NODE_UNIQUE_NAME_2 MSOLAP_NODE_SCORE
1 3 Date First Purchase
1 0 Bike Buyer
1 2 Commute Distance
1 5 English Occupation
1 4 English Education
1 1 Birth Date
2 5 0 1
2 2 0 4
2 3 0 5
2 1 0 3
2 4 0 2

NODE_TYPE 1 is a node, NODE_TYPE 2 is an edge.

Node "Date First Purchase" has an ID 3, "Bike Buyer" has an ID 0, "Commute Distance" has ID 2 etc.

The strongest edge with MSOLAP_NODE_SCORE = 5 goes from node 3 to node 0, i.e. from "Date First Purchase" to "Bike Buyer".

Tatyana

|||Great thx a lot... it helps me more than you can imagine. Take care Tatyana

Langsuyuar

Dependency network question!!

Hi, I have a question concerning microsoft data mining in sql server 05.

The thing is: when i create a new AS project in my visual studio define data source view and create model, let it be decition tree, i can see "dependency network" where i can figure how one column relate to another.

I want to use this knowledge in my C# application in order to choose right column to my next task. How do i get the result using dmx or amo? Do you have any idea?

Run SQL Server Profiler when opening dependancy network tab of the viewer. You will see what store procedure the viewer calls to display the dependency network. Then call the store procedure from your C# application.

Here is what you get back from the store procedure. (I executed

CALL System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('v Target Mail', 60) )

NODE_TYPE NODE_UNIQUE_NAME_1 NODE_UNIQUE_NAME_2 MSOLAP_NODE_SCORE
1 3 Date First Purchase
1 0 Bike Buyer
1 2 Commute Distance
1 5 English Occupation
1 4 English Education
1 1 Birth Date
2 5 0 1
2 2 0 4
2 3 0 5
2 1 0 3
2 4 0 2

NODE_TYPE 1 is a node, NODE_TYPE 2 is an edge.

Node "Date First Purchase" has an ID 3, "Bike Buyer" has an ID 0, "Commute Distance" has ID 2 etc.

The strongest edge with MSOLAP_NODE_SCORE = 5 goes from node 3 to node 0, i.e. from "Date First Purchase" to "Bike Buyer".

Tatyana

|||Great thx a lot... it helps me more than you can imagine. Take care Tatyana

Langsuyuar

Dependency Network Graph Question

I have some problems in understanding the dependency network graph which is built in the association rules algorithm. My questions are:

1) Assuming an arrow goes from Node A to Node B ( A --> B), is Node A dependent from Node B or is Node B dependent from Node A?
2) Are only rules used with one element on each side of the rule (itemset size = 2) or is the graph also influenced by rules with more elements?
3) it is said that the slider on the left side is related to the probability of the rules. when viewing my rules table I don't understand why some rules are viewable later then other ones because they have the same probability. Is there another parameter influencing this slider? Which one?

I would be grateful for your help!

Regards, David

1: read it as A -> B, that means that the presence of A imples the presence of B.

2: The dep net currently only uses rules generated from two itemsets

3: I believe the slider is more the importance of the rule than the probability. A rule A->B where both occur 100% in the data isn't very interesting, so I think that's what you are seeing.

Dependency Net Viewer

Hi,

I've got a Decision Tree model and I'm trying to browse the dependency network. Apparently, there are too many nodes and they don't all show up in the view. I know that I can find hidden nodes, but is there a way to show all w/o having to add them one by one to view? How does the UI determine which ones to filter out?

Thanks,

Mike

Excellent question!

The dependency net viewer takes the top 60 nodes determined by a heuristic to be interesting. It uses a mix of input and output nodes to make sure that you don't get all inputs or all outputs (that wouldn't make an interesting diagram, would it?)

Using the dependency net viewer control, you can only add nodes one by one. However, if you use the Data Mining Templates for Visio 2007 (part of the SQL Server 2005 Data Mining Addins for Office 2007 - preview currently available here) you can create a dependency net with however many nodes you like. You can also "grow" a network dynamically by searching for a node and then adding related nodes and then adding related nodes to the related nodes and so on. The preview build requires the Beta 2 Technical Refresh version of Office 2007 to run - in particular for this case, the Beta 2 Technical Refresh of Visio 2007.

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)

Dependency Info

I am under the impression that either there are deficiencies in the way SQL
Server 2000 is keeping track of depedencies or perhaps the dependencies
simply need to be re-evaulated / recompiled.

What is the best and/or easiest way to ensure that the dependency info is
accurate?

Is there a way to tell MSSQL to rebuild/reexamine them all?

Thanks in advance to the MVPs and other generous and knowledgable posters
who have helped me recently.

ChadChad (chad.dokmanovich@.unisys.com) writes:
> I am under the impression that either there are deficiencies in the way
> SQL Server 2000 is keeping track of depedencies or perhaps the
> dependencies simply need to be re-evaulated / recompiled.
> What is the best and/or easiest way to ensure that the dependency info is
> accurate?

To forget about it!

Seriously, the nature of SQL Server makes it very difficult for the
server to really keep track of dependencies. If you drop a procedure and
recreate it - of course now SQL Server cannot know which procedure
that refers to it. (The same happens if you alter a procedure. Now, that
is stupid.) Also, SQL Server permits you to create a procedure that
refers to a table that does yet not exist. And you can drop and recreate
a table.

In my daily work, I often use the dependency information to get a
quick information about references to tables and individual columns.
I often have access to an empty database that I have built from scripts,
so this information should be correct. (Since my build tools loads tables
before procedures, and anyway tells me if I refer to something non-
existing.

But if I am looking for where a procedure is being referenced, I run
a tool that searchces SourceSafe. In that case I am also interested in
references from client code, so SQL Server would not be sufficient, even
if the information was correct and complete.

> Is there a way to tell MSSQL to rebuild/reexamine them all?

Not really. What you can do is to generate scripts for your database
and then build a database from that script. But since that script
would sort the objects according to the known dependency information,
you might still not get everything loaded in the right order.

(And anyway, you should have all your source code under version control.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I check dependencies in several ways.

The least effective is to check sysdepends. As Erland pointed out,
it's not usually up-to-date or complete in the face of schema changes.
There are some ways to keep it more up-to-date though. A major problem
with sysdepends is that it doesn't keep track of cross-database
references.

You can also do a text search of stored procedure code via syscomments.

The best approach for checking dependencies I have found is to simply
script out the entire database to text files, then use a text search
utility to search for table or procedure names. This is fast and
complete. I use textpad to "find in files", it works really well. I
automatically script the databases out every day to have a current
snapshot.

My dream is to have a T-SQL parser which reads through all the code and
compiles exactly which procs, tables and fields are being used and
which operations are being done on them. And compile the results into
a set of user tables. Some day.

Dependency file locations for a SSIS package

Hi all,

I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).

Thanks,

Johan

Johanw581996 wrote:

Hi all,

I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).

Thanks,

Johan

Absolute paths are required when pointing to things external to the package (XML configuration files, etc...), so the path should never change from when the package was developed.

Dependency based scheduling

Hi,
I am scheduling a .bat at 9.00 am to load a text file in a table A in SQL Server 2000 database. If this load is successful, I want to trigger another .bat file to execute a store proc.

Can I do this dependency based scheduling in SQL Server Scheduler and if so, can you give me details (references, books) as to how it can be done?

Thanks in advance
ShekI'd create a job with a couple of job steps. SQL server Books Online has info on sp_add_job and sp_add_jobstep. You can use XP_CMDSHELL to execute the bat files within the job steps.|||The job scheduler supports multipule tasks within one job. You can specify what action should be taken based on the success or failure of a task.

I susspect you could work through this just by setting up a test job in the job scheduler. Books Online, SQL Server Help, has a good writeup on this.

Look up "jobs" in the index. Also within jobs scan down to the sub topic of steps.

Dependencies

how and when are dependencies created. I know when tables are linked in a
view then a dependency is created what about stored proceedures?
Thanks
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks
|||The sp_rename will give you all sorts of errors about breaking dependencies
as well.
Sincerely,
Anthony Thomas

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uNzDMJb2EHA.2568@.TK2MSFTNGP10.phx.gbl...
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks

Dependencies

how and when are dependencies created. I know when tables are linked in a
view then a dependency is created what about stored proceedures?
ThanksDependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks|||The sp_rename will give you all sorts of errors about breaking dependencies
as well.
Sincerely,
Anthony Thomas
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uNzDMJb2EHA.2568@.TK2MSFTNGP10.phx.gbl...
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks

Dependancy Information

Is there a way to update the dependency information
For example if I create a SP that references a table that does not exist no record will be put in sysdepends. If I later add this table can I update the dependancies to reflect this change?the only way I can think is to run Alter procedure script
in QA, or open the SP in Enterprise Manager and add a
blank line anywhere. This will update the sysdepends table.
>--Original Message--
>Is there a way to update the dependency information?
>For example if I create a SP that references a table that
does not exist no record will be put in sysdepends. If I
later add this table can I update the dependancies to
reflect this change?
>.
>