Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

Get all referencing tables of a table in Sql Server

Posted on December 17, 2019 By

Today I want to talk about how to get all referencing tables of a Sql Server table.

It is useful, for example, if you need to delete a certain record but you can’t because is referenced by many other tables.

In short we will see how to accomplish that…

First of all we need a query to get a recordset with all the referencing tables.

We can get the information we need accessing system catalog views provided by Sql Server.

The query is as follow:

SELECT t.Name AS REFERENCING_TABLE, 
       c.name AS REFERENCING_TABLE_FIELD
FROM sys.foreign_key_columns fk
INNER JOIN sys.tables as t on fk.parent_object_id = t.object_id
INNER JOIN sys.tables as rt on fk.referenced_object_id = rt.object_id
INNER JOIN sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
WHERE rt.name = 'PARENT_TABLE' -- <== THIS IS THE REFERENCED TABLE, REPLACE WITH YOUR TABLE

The query above returns all the referencing tables of the table “PARENT_TABLE”.

Now suppose you get the following result:

REFERENCING_TABLE      REFERENCING_TABLE_FIELD
---------------------- -----------------------
CHILD_TABLE_1            ID1
CHILD_TABLE_2            ID2
CHILD_TABLE_3            ID3

and in table PARENT_TABLE you have a table with ID=3 that you want to delete.

You could use this script to generate the delete statements for the child tables:

SELECT 'DELETE FROM ' + t.Name + ' WHERE ' + c.name + ' = 3' AS DELETE_STATEMENT
FROM sys.foreign_key_columns fk
INNER JOIN sys.tables as t on fk.parent_object_id = t.object_id
INNER JOIN sys.tables as rt on fk.referenced_object_id = rt.object_id
INNER JOIN sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
WHERE rt.name = 'PARENT_TABLE' -- <== THIS IS THE REFERENCED TABLE, REPLACE WITH YOUR TABLE

And here you can see the result:

DELETE_STATEMENT
---------------------------------------
DELETE FROM CHILD_TABLE_1 WHERE ID1 = 3
DELETE FROM CHILD_TABLE_2 WHERE ID2 = 3
DELETE FROM CHILD_TABLE_3 WHERE ID3 = 3

Now you can simply execute the delete statements to remove records in child tables, and the you can delete the record in the parent table.

See you soon!!

Sql Server, Sql Server 2016

Post navigation

Previous Post: Public key authentication in a Windows SFTP Server
Next Post: Capture screen using C#

Related Posts

Sql Server error: A severe error occurred on the current command.  The results, if any, should be discarded. Sql Server
SQL Server and SARGables predicates Sql Server
Sql Server add constraint WITH NOCHECK Sql Server
Sql Server: apply an aggregate function to fields values in a row Sql Server
SQL Server LEN function return 0 passing a string with one space Sql Server
Improving SQL Server cursor performance Sql Server

Search

Related posts

  • Delete all rows from all tables in a Sql Server database
  • Load a huge amount of data in a Sql Server table
  • Get SQL Server version in T-SQL
  • Reset Sql Server cache

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark