SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Referential Integrity Tree
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

klca
Starting Member

17 Posts

Posted - 10/02/2010 :  02:13:39  Show Profile  Reply with Quote
Hola,

I'd like to know the way in which I could build a Referential Integrity Tree which allow me to know the way in which tables are related among them.

So I'd expect to have: table name, column name, type of key (primary, foreign), type, related table name, related column name, and all other that could help me build the tree (inm script and in diagrams if possible)

Carlos Porras (El Salvador)

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/02/2010 :  10:07:59  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Here's a tool I made for debugging a 3rd party's schema. Maybe it will be usfull to you as a starting off point.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143772

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/02/2010 :  22:46:32  Show Profile  Reply with Quote
Hola,
Thanks a lot for your kind and prompt answer.

I just run it at once and it gave me back 101379 rows .... !!!!

I'll try to "digest" all this colume of information and see how can I make it usable for the purpose of developing some "cascading" system in an ASP.Net application running under ASP.Net Dynamic Data 4.0.

I'm trying to get a way of implementing "reactive" dropdownlists so when the end user changes one of them while "Bulk Inserting Data" (call it introducing records of the same kind for which I'll be allowing the end user to carry on all previous selected dropdownnlist values from a previous record inserted)

So my goal is to make "changes in selected values" translated into proper display of "Referential Integrity" for all depending dropdownlists.

Thanks a lot


Carlos Porras (El Salvador)

quote:
Originally posted by Transact Charlie

Here's a tool I made for debugging a 3rd party's schema. Maybe it will be usfull to you as a starting off point.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143772

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/02/2010 :  23:44:54  Show Profile  Reply with Quote
Hola,

After revising results I found out that there are many rows which seems to have "multiplicity"

e.g.:

t21tbl_depts t30tbl_lines [cod_dept] [t47tbl_sections].[cod_dept] t21tbl_depts -> t47tbl_sections -> t30tbl_lines RelNum14
t21tbl_depts t30tbl_lines [cod_sect] [t47tbl_sections].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines RelNum14
t21tbl_depts t31tbl_LineAttribs [cod_dept] [t30tbl_lines].[cod_dept] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t31tbl_LineAttribs [cod_line] [t30tbl_lines].[cod_line] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t31tbl_LineAttribs [cod_sect] [t30tbl_lines].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t31tbl_LineAttribs [cod_dept] [t30tbl_lines].[cod_dept] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t31tbl_LineAttribs [cod_sect] [t30tbl_lines].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t31tbl_LineAttribs [cod_line] [t30tbl_lines].[cod_line] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107
t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107


quote:
Originally posted by Transact Charlie

Here's a tool I made for debugging a 3rd party's schema. Maybe it will be usfull to you as a starting off point.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143772

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/03/2010 :  13:09:07  Show Profile  Reply with Quote
Hola,

Using TextPad I could reduce amount of records in output (more than 100,000) to 1/3rd of it. Now I just got 36,000 for a little more than a 100 tables in my database schema.

But more than fixing the script as to avoid obtaining multiplicity I'd like to understand the logic with which the results must be read.

How do you interpret each line of the output? How can you derive cascading from there (I know the result shows a chain of relations but how do you think it can be implemented in a real life situation, lets say in NorthWind I'm interested in the table "Products" ... how do I start from there and I have already set that value in your script and I have already gotten the corresponding output?


Carlos Porras (El Salvador)



quote:
Originally posted by Transact Charlie

Here's a tool I made for debugging a 3rd party's schema. Maybe it will be usfull to you as a starting off point.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143772

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION



Edited by - klca on 10/04/2010 11:48:30
Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/04/2010 :  11:50:05  Show Profile  Reply with Quote
Hola,

Back again. Charlie (or anyone else interested in this matter), do you think you can help me with this, plese?

Carlos Porras (El Salvador)


quote:
Originally posted by Transact Charlie

Here's a tool I made for debugging a 3rd party's schema. Maybe it will be usfull to you as a starting off point.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143772

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/04/2010 :  14:18:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Hi klca,

Sorry for not replying sooner -- I've been busy.

There are comercial tools that will do what you want but I think they are all pretty expensive. The script I wrote is a very simple cascade report and it doesn't deal with compound keys and other factors.

I was really providing it as a starting off point for you to write your own or to get a quick overview of the relationships.

I've no idea what your schema is like (It sounds like neither have you) but I'm guessing that there are maybe multiple keys between the same tables / compound keys?

My advice is pick a table and have a look at the keys in management studio, see if anything obvious falls out.

I don't have the time (or more importantly the need) to change the script, it worked for what I wanted to do which was to work out how to delete and insert data into a closed third party schema. I'm certainly not going to play about with it in adventureworks.

On the other hand, feel free to take it and rewrite it to your requirements and maybe, someone with more time would be able to.

Also -- Peter wrote a blog post on how to script out the foreign keys from a database (which is very useful if you need to change columns involved)
http://weblogs.sqlteam.com/peterl/archive/2009/08/07/Script-out-your-foreign-keys.aspx

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/04/2010 :  15:04:30  Show Profile  Reply with Quote
Hi,

I just want to thank you for your help.

I was not implying by any means that you should rework your query.

It has been very helpful as it is right now.

I was just wondering the way in which you "read/interpret" the results and based on your answer I would be able of writing complementary queries as to make proper implementation of those results in my model more efficiently.

Thanks again for your valuable help


Carlos Porras (El Salvador)

quote:
Originally posted by Transact Charlie

Hi klca,

Sorry for not replying sooner -- I've been busy.

There are comercial tools that will do what you want but I think they are all pretty expensive. The script I wrote is a very simple cascade report and it doesn't deal with compound keys and other factors.

I was really providing it as a starting off point for you to write your own or to get a quick overview of the relationships.

I've no idea what your schema is like (It sounds like neither have you) but I'm guessing that there are maybe multiple keys between the same tables / compound keys?

My advice is pick a table and have a look at the keys in management studio, see if anything obvious falls out.

I don't have the time (or more importantly the need) to change the script, it worked for what I wanted to do which was to work out how to delete and insert data into a closed third party schema. I'm certainly not going to play about with it in adventureworks.

On the other hand, feel free to take it and rewrite it to your requirements and maybe, someone with more time would be able to.

Also -- Peter wrote a blog post on how to script out the foreign keys from a database (which is very useful if you need to change columns involved)
http://weblogs.sqlteam.com/peterl/archive/2009/08/07/Script-out-your-foreign-keys.aspx

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

klca
Starting Member

17 Posts

Posted - 10/06/2010 :  13:48:07  Show Profile  Reply with Quote
Hola,

And thanks again. Maybe you can just help me with a little follow up.

Carlos Porras (El Salvador)


quote:
Originally posted by Transact Charlie

Hi klca,

Sorry for not replying sooner -- I've been busy.

There are comercial tools that will do what you want but I think they are all pretty expensive. The script I wrote is a very simple cascade report and it doesn't deal with compound keys and other factors.

I was really providing it as a starting off point for you to write your own or to get a quick overview of the relationships.

I've no idea what your schema is like (It sounds like neither have you) but I'm guessing that there are maybe multiple keys between the same tables / compound keys?

My advice is pick a table and have a look at the keys in management studio, see if anything obvious falls out.

I don't have the time (or more importantly the need) to change the script, it worked for what I wanted to do which was to work out how to delete and insert data into a closed third party schema. I'm certainly not going to play about with it in adventureworks.

On the other hand, feel free to take it and rewrite it to your requirements and maybe, someone with more time would be able to.

Also -- Peter wrote a blog post on how to script out the foreign keys from a database (which is very useful if you need to change columns involved)
http://weblogs.sqlteam.com/peterl/archive/2009/08/07/Script-out-your-foreign-keys.aspx

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/07/2010 :  04:25:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Hi Klca.

I'll try and help of course. However, I'm pretty busy at work so if you can ask a specific question I'll do my best.

please remember that I have absolutely no clue as to what your schema is like.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000