August 28, 2008 at 10:10 am
I need to develop a list of tables that do not have a foreign key constraint on them (stand alone), any ideas on how to do this?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 10:17 am
August 28, 2008 at 10:23 am
That gave me just the opposite. That is every table with a Foreign Key i want every one without. But then again I could take that temp table and then look for any table name not in the temp table and that should give me what I want.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 10:28 am
SELECT name
FROM sysobjects
WHERE xtype='U'
AND objectproperty(object_id(name),'TableHasForeignKey')= 0
ORDER BY 1
August 28, 2008 at 10:45 am
Close but is there way to add in if it's referenced by a foreign key? Example:
Customer has no foreign key but is referenced by the Employee table.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 3:55 pm
Arthur.Lorenzini (8/28/2008)
Close but is there way to add in if it's referenced by a foreign key? Example:Customer has no foreign key but is referenced by the Employee table.
Referenced by what?
_____________
Code for TallyGenerator
August 28, 2008 at 4:02 pm
By another table. I know the Customer table does not have foreign keys on it but the employee table referenece the Customer table.
So
SELECT name
FROM sysobjects
WHERE xtype='U'
AND objectproperty(object_id(name),'TableHasForeignKey')= 0
ORDER BY 1
Returns the customer table in the list but it really isn't a stand alone table because it referenced by the employee table.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 4:32 pm
Arthur.Lorenzini (8/28/2008)
but it really isn't a stand alone table because it referenced by the employee table.
Who says it's referenced from the employee table?
What is the evidence that such reference exists?
_____________
Code for TallyGenerator
August 28, 2008 at 5:42 pm
Because it has a foreign key to Customer table.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 6:26 pm
Then you need just read what's posted, fight your laziness and open BOL ("help" for SQL Server, F1), find topic about OBJECTPROPERTY and find parameter TableHasForeignRef next to TableHasForeignKey.
I guess the rest is obvious.
_____________
Code for TallyGenerator
August 29, 2008 at 6:19 am
Thank you for the information which helped and the candid insights to my behavior.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply