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)

deny truncate?

What if I want to give users Query Analyzer-style access to my data, but
don't want them to delete it?
I can do
deny delete on dbo.mytable to public
which is great... assuming they don't know how to do 'truncate table'.
uh?
is there any way I can prevent users from truncating a table by permissions?Only members of the sysadmin server role and the db_owner and db_ddladmin
database roles and the table owner have permissions to Truncate the table.
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
> permissions?|||> is there any way I can prevent users from truncating a table by
permissions?
Yes, don't make them administrators or dbo.
http://www.aspfaq.com/
(Reverse address to reply.)|||From Books Online:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
sysadmin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
The bottom line: Manage security properly and the users will not have
rights to do anything that you do not want them to.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
permissions?|||OK...
We have implemented a rule that any developer that needs to create tables in
a database has to be members of the db_owner role of the database as they
need to be able to create dbo. prefixed tables, as if they can't, then one
will create a table and it will be called, say
mydb.dave.davestable
and then when, say Julie, selects, she will effectively get the results from
mydb.julie.davestable
which either won't exist or else she won't get the same results as dave...
I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.
e.g. I need to create a dbo. procedure, and I need to be able to create
another dbo.procedure, without having to spend time setting users permission
s
from it whenever I create a new procedure.
"Bonj" wrote:
[vbcol=seagreen]
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by permissions?[/vbco
l]|||No problem as long as you have separate development and production
environments and the developers are not a member of the db_owner role on the
production environment?
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...[vbcol=seagreen]
> OK...
> We have implemented a rule that any developer that needs to create tables
> in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
> from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
> permissions
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>|||>>I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.<<
They do not need to be dbo to execute a dbo owned stored procedure. Just
have dbo issue
GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
You can easily create a stored procedure that issues the appropriate grant
statements to the appropriate users on all stored procedures or on a subset
of stored procedures based on creation date or name. You decide what you
need and create the supporting stored procedure to do it.
Giving users the ability to create tables...I would not give them dbo
rights for this. I would let them play in their own "sandbox" by creating
tables under their account. When their sand castle (or table) has been
approved then dbocan create the table within the development environment (as
dbo).
I have used the same techniques in the past with much success.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
> OK...
> We have implemented a rule that any developer that needs to create tables
in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
permissions[vbcol=seagreen]
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>
permissions?|||erm.... nah, that wouldn't really work.
You see, the thing is we *do* have separate development and production
environments... slightly (tries to keep a straight face). No, we do.
It's just that most things will need further development after they've been
put into production. In fact, I tend to rate a system as good if it needs
very little time spent developing it after it's been put into production,
although the contrary doesn't necessarily mean it's bad. However, I digress.
To put it diplomatically, when forming an expectation as to how long it will
be before the results of a 'requested improvement' will be seen, the
requester is likely to look on the optimistic end of the scale of how much
testing will be required. Consequently, code changes are made in production
environment - so the opportunity to use the results as real if the 'testing'
all passes, doesn't need to be missed. At the same time, if a production
server is busy with several jobs, other ad-hoc systems but that are
officially 'in production' will overflow onto one of the spare 'development'
servers.
Hence, it makes no sense to *force* a certain server to be either *always*
development or *always* production.
Besides, if a system is in production, the amount of use through Query
Analyzer it gets is less than one that's in development, so people are
unlikely to accidentally truncate the table anyway, but they are when in
development.
I think the most likely cause of the data getting deleted is the database
being recompiled. But then, that's a good thing, as it ensures one run
doesn't inherit the previous one's values. But I could go for miles down
that road - which is at the end of the day a lot like writing a load of
error handling code which might never be called.
but thanks for your help anyway
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23pmvt3TsEHA.1336@.tk2msftngp13.phx.gbl...
> No problem as long as you have separate development and production
> environments and the developers are not a member of the db_owner role on
the
> production environment?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
tables[vbcol=seagreen]
they[vbcol=seagreen]
one[vbcol=seagreen]
dave...[vbcol=seagreen]
and[vbcol=seagreen]
without[vbcol=seagreen]
but[vbcol=seagreen]
>|||> They do not need to be dbo to execute a dbo owned stored procedure. Just
> have dbo issue
> GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
I know, the thing is though, typing out all those statements could be
regarded as work that isn't actually getting anybody anwhere that they
couldn't be just put there by flipping a switch once. There isn't a large
amount of developers, only 5 - in fact there's twice as many servers than
developers. I just thought if it was easy, then I could do it without too
much hassle.

> You can easily create a stored procedure that issues the appropriate grant
> statements to the appropriate users on all stored procedures or on a
subset
> of stored procedures based on creation date or name. You decide what you
> need and create the supporting stored procedure to do it.
That's an option - it does need repeatedly calling, i.e. every time the
database runs, though.

> Giving users the ability to create tables...I would not give them dbo
> rights for this. I would let them play in their own "sandbox" by creating
> tables under their account. When their sand castle (or table) has been
> approved then dbocan create the table within the development environment
(as
> dbo).
Don't get me wrong - I'm not the administrator. I'm a developer.
The 'sandbox' approach wouldn't really work though, because all too often
than not, we have to use the results of what another developer has done,
which is thus in his table. The 'users' of the system, as such, don't even
get to see SQL server. All they see are Excel sheets and graphs, and various
things such as crystal reports in pdf format, that are downloaded off the
servers.
Cheers|||> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once.
Do you use source control? Do you store your stored procedures within
script files within that source control system? If so just add the
appropriate command to the stored procedure script files. Each time the
stored procedure is compiled (created) from the script file the appropriate
grant statement(s) will be executed.

> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
No, it needs to be called every time a stored procedure is dropped and
created (or newly created).
The sandbox approach would only apply for a very short development cycle.
You develop a table and a stored procedure in your sandbox. You get it
working. You get your DBA to create the table and stored procedure(s) as
dbo and you are done.
Keith
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:OqrBpjVsEHA.2128@.TK2MSFTNGP11.phx.gbl...
Just[vbcol=seagreen]
> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once. There isn't a large
> amount of developers, only 5 - in fact there's twice as many servers than
> developers. I just thought if it was easy, then I could do it without too
> much hassle.
>
grant[vbcol=seagreen]
> subset
you[vbcol=seagreen]
> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
>
creating[vbcol=seagreen]
> (as
> Don't get me wrong - I'm not the administrator. I'm a developer.
> The 'sandbox' approach wouldn't really work though, because all too often
> than not, we have to use the results of what another developer has done,
> which is thus in his table. The 'users' of the system, as such, don't even
> get to see SQL server. All they see are Excel sheets and graphs, and
various
> things such as crystal reports in pdf format, that are downloaded off the
> servers.
>
>
>
>
>
> Cheers
>