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 |
|
Seregwethrin
Starting Member
6 Posts |
 Posted - 2010-06-28 : 08:06:23
|
| Hello,Think there are hundreds of tables and you're creating the database (problem is used with altering too or altering )Tables are referencing each other. Of course some of them references more than 1 and some of them don't reference any table.So when creating the tables, the table which has no reference to others should be created first.When we think of that, a solution is mapping algorithm to start one node which has no reference and go from there.But than the problem is how can we get if the table has a reference and if has, to which table just from sql query (as string)?Or, are there any other solutions to this problem? Is there any solution in .NET? |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 08:22:32
|
| Do you want refrences of just the tables or all the objects associated with that table?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
Seregwethrin
Starting Member
6 Posts |
Posted - 2010-06-28 : 08:26:48
|
| Just references needed for now, which is I'm wondering how.But I'm also wondering how to get all objects associated with that table tooBy your answer, should I understand there's no other solution to this problem than mapping tables as nodes by their references? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 08:54:59
|
Well you can use the query below to get the primary table and the foreign tables mapped.select P.name as primary_table, o.name as foreign_table from sysobjects f inner join sysobjects o on f.parent_obj = o.id inner join sysreferences r on f.id = r.constid inner join sysobjects p on r.rkeyid = p.id ORDER BY p.name To get the objects dependent on a table you can simply use a system SP EXEC sp_depends 'Your Table Name' Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
Seregwethrin
Starting Member
6 Posts |
Posted - 2010-06-28 : 09:02:12
|
| Thank you Idera, it will be helpful. |
|
|
|
Seregwethrin
Starting Member
6 Posts |
Posted - 2010-06-28 : 09:10:35
|
| Btw, what about getting this information from an sql query (as string)?Should I get it without executing it? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 09:10:52
|
quote:
Originally posted by Seregwethrin
Thank you Idera, it will be helpful.
Welcome  Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-28 : 10:11:06
|
| The script on the link below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.Find Table Reference Levelshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957CODO ERGO SUM |
|
|
|
Seregwethrin
Starting Member
6 Posts |
Posted - 2010-06-28 : 10:42:33
|
| I guess we still can't learn referencing tables from the sql query not from the system tables |
|
|
|
|
|
|
|
|
Which table should be created first, how to find?