none
Database Visualization Tools

    Question

  • Hello MSDN Hive Mind,

    This is a newb question, but help would gratefully appreciated.

    What tools are available for 2005 SQL Server Management Studio that allows developers to view the dependencies within a database?

    I am taking over a project with no documentation and am seeking a way to view the FK within the database without having to go through every single table in the tree.

    Monday, October 04, 2010 4:58 PM

Answers

All replies

  • Declare @TableName sysname
    Set @TableName = 'myTestTable'
    
    SELECT 
       TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE, 
       TC.CONSTRAINT_NAME AS PRIMARYKEY, 
       COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY, 
       (CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A' ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME END) As FOREIGNKEYTABLE
    FROM 
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 ON TC.CONSTRAINT_NAME = RC1.UNIQUE_CONSTRAINT_NAME
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME = RC1.CONSTRAINT_NAME
    WHERE 
       TC.CONSTRAINT_TYPE ='PRIMARY KEY' 
       And TC.TABLE_NAME = @TableName
    ORDER BY 
       TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME
    
    

    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Monday, October 04, 2010 7:45 PM
  • Hi Ben,

    You can also generate database diagram if you want to see the visual representation. Select "Database Diagrams" under "Database" in the Management studio and choose "New Diagram" in the context menu (right mouse click).


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 04, 2010 7:49 PM