Where Used Statements in SQL

Question

I have data used primarily in one table in a database and referenced in several different tables. I need to correct the data. Is it possible to write a "where-used" statement that will search every table in the database and return all tables where the data is referenced?

I am using SQL 2005.

Thanks.

Answer 1

Try adding a diagram to the database, and drop all of the tables onto it. If I've interpreted your question correctly you're trying to understand a database schema that already exists? IF you use the diagram it will draw on the references for you which will allow you to see where the data is linked in your table structure.

As for SQL you can use joins, or where conditions to link data from different tables.

What are you trying to "correct"?

Answer 2

I've found this sql statement here:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND  OBJECT_NAME(OBJECT_ID) LIKE 'FK_%'

NOTE:- I name all foreign key constraints starting with FK* so it is easy to filter them.

Answer 3

In Management Studio you can right click on a table/view/stored procedure and select View Depenencies. In the dependencies window you can choose to view objects that the selected item depends on or view the items which depend on the selected item.

Answer 4

You can't do it after the fact, but at the table design time you can set up relationships to "ON UPDATE CASCADE".

Answer 5

You could reverse engineer the database (see this posting for a script that does this) if it has foreign keys physically present in the database. If this is not the case then you are up for some manual detective work.

Visio professional has a tool for reverse engineering a database. 2003 doesn't play nicely with SQL Server 2005, so you will need either:

• Visio 2007

• Modify the script linked to above so it doesn't generate schema references and load the script into a SQL Server 2000 database. Then, reverse engineer from the SQL 2000 DB.

If you don't have foreign keys or have incomplete foreign key coverage, you can look for them manually (for example, look for fields with similar looking codes to reference data) and manually annotate the keys in the diagram. Don't try to do this with the diagramming tool that comes in SSMS as it can attempt to populate the FKs back into the database.