Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
klca
Starting Member
17 Posts |
 Posted - 2010-10-02 : 02:13:39
|
| 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
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-02 : 10:07:59
|
| 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=143772Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-02 : 22:46:32
|
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 lotCarlos 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=143772Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-02 : 23:44:54
|
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 RelNum14t21tbl_depts t30tbl_lines [cod_sect] [t47tbl_sections].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines RelNum14t21tbl_depts t31tbl_LineAttribs [cod_dept] [t30tbl_lines].[cod_dept] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t31tbl_LineAttribs [cod_line] [t30tbl_lines].[cod_line] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t31tbl_LineAttribs [cod_sect] [t30tbl_lines].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t31tbl_LineAttribs [cod_dept] [t30tbl_lines].[cod_dept] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t31tbl_LineAttribs [cod_sect] [t30tbl_lines].[cod_sect] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t31tbl_LineAttribs [cod_line] [t30tbl_lines].[cod_line] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs RelNum90t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107t21tbl_depts t23tbl_itemdetails [cod_lineAttrib] [t31tbl_LineAttribs].[cod_lineAttrib] t21tbl_depts -> t47tbl_sections -> t30tbl_lines -> t31tbl_LineAttribs -> t23tbl_itemdetails RelNum107quote:
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=143772Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-03 : 13:09:07
|
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=143772Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-04 : 11:50:05
|
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=143772Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-04 : 14:18:09
|
| 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.aspxGood luck.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-04 : 15:04:30
|
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.aspxGood luck.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
klca
Starting Member
17 Posts |
Posted - 2010-10-06 : 13:48:07
|
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.aspxGood luck.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-07 : 04:25:24
|
| 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Referential Integrity Tree