November 5, 2009 at 6:12 am
Hi all. My question is how do you, when coming into a new environment where you are not familiar with the databases and how tables relate to each other, find out which tables are related to each other. Is there a relatively simple way of telling this other than going into each table and finding out the primary and foreign keys or right clicking and finding their dependencies? If a database has hundreds of tables this would be a daunting task.
Any help would be greatly appreciated.
Thanks,
Gabe
November 5, 2009 at 7:03 am
Try this and see if it gives you want you need. Contribued by a member of SQLServerCentral whose name I failed to record
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListForeignKeyConstraints]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ListForeignKeyConstraints]
AS
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, ''CNSTISDISABLED'') = 0 THEN ''ENABLED''
ELSE ''DISABLED''
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
'
END
November 5, 2009 at 8:52 am
bitbucket-25253, Thanks for your post! Let me know if this is asking to much but can you explain the stored procedure? I did try it on the Northwind database and I did get results but I am not sure I understand what I am looking at. I tried on a database that was created for us by a vendor and it returned no results. Thanks in advance for your help
November 5, 2009 at 10:36 am
Look in Books On Line (BOL) at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41544236-1c46-4501-be88-18c06963b6e8.htm
It will explain in great detail the information available thru the T-SQL previously posted.
Then move on to the new system views available in SQL 2005 at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7247f065-5441-4bcf-9f25-c84a03290dc6.htm
and then
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-34c1b719ac2c.htm
It hardly seems likely that your vendor's database does not have foreign keys but this T-SQL will list all tables without foreign keys.
SELECT name [Tables NOT having FK]
FROM sysobjects
WHERE xtype='U'
AND objectproperty(object_id(name),'TableHasForeignKey')= 0
ORDER BY 1
If all of the above does not clarify the subject for you post back and we will see what other assisance we may offer you.
Edited at 12:56
What security does the vendor DB have? What role/permission are you granted when you login?
November 5, 2009 at 12:27 pm
Thanks for the SQL that tells me about the tables not having a FK relationship. Here are the results. There are 107 tables with no foreign key. Now I am not an expert in database design but I would say that is not a good design. Don't you have to define the FK relationship when you design the table?
November 6, 2009 at 12:36 pm
gboyer (11/5/2009)
Now I am not an expert in database design but I would say that is not a good design. Don't you have to define the FK relationship when you design the table?
You'd be correct, that is a poor design. And no, foreign keys are not required.
November 7, 2009 at 9:36 am
gboyer (11/5/2009)
Now I am not an expert in database design but I would say that is not a good design
.
Like many questions about SQL Server the answer is "It all depends" In this case the vendors point of view. Microsoft opted to design a product to be utilized by IT professionals to provide support for business needs, and as such the tool is exceptional. Now for those business without a IT staff or a limited staff, yet still with unsatisfied business needs, a vendor supplied tool may yield the best return on investment (ROI). From the vendors view continued sales/support means their success or failure as a business. Your vendor may have deliberately chosen to enforce data integrity in their software products interface, in attempt to establish a continuing business, by making it difficult for the purchaser to make modifications or enhancements. Your firm returns to the vendor for a specific enhancement, the vendor cuts a deal with your company to provide the enhancement at say a lower cost provided that they can market the enhancement to other firms. In effect your firm pays the development costs and the vendor reaps additional profit by selling the enhancement to other users, or the enhancement itself attracts new buyers to the product.
From the purists point of view it is not the best design, but like I stated earlier "It depends"
November 9, 2009 at 3:20 am
itried it but it give this error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CREATE PROCEDURE [dbo].[ListForeignKeyConstraints]
AS
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0'.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply