• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Finding constraints through code

Is there a way to find a record in a table is enforced by any existing constraints.

For example, tableB has ColumnA that is used as a FK in 7 other tables.

I want to check quickly check if there are existing dependent data in any of the 7 tables. I can test it by trying to delete the record, and if there are constraints, it will complain. But is there any other way to see/check this?

thanks
0
anushahanna
Asked:
anushahanna
  • 2
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
use sp_fkeys

The following example retrieves a list of foreign keys for the HumanResources.Department table in the AdventureWorks database.

USE AdventureWorks;
GO
EXEC sp_fkeys @pktable_name = N'Department'
    ,@pktable_owner = N'HumanResources'
 
0
 
anushahannaAuthor Commented:
is it possible to see how many rows in each of these FK tables has entry for the PK in the original table?
0
 
waltersnowslinarnoldCommented:
Use the following code..

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME,  
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
you have to write a query using the joins (no other option )
0
 
waltersnowslinarnoldCommented:
For your 2nd need, use the following..,

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
0
 
anushahannaAuthor Commented:
waltersnowslinarnold's gives me the same info as the sp_fkeys.

you are right, I will have to do a join.
thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now