Find what Foreign keys are in use

Question

I have about 50 tables with created-by and changed-by referencing the user table. When I want to delete the user, I would like to create a list of what tables this user has data in, so one can go delete/edit everything belonging to said user. Is there any way of doing this in SQL?

check out my answer it does what you need – KM. Apr 06 '09 at 12:58 — KM., Apr 06 '09 at 12:58

score 3 · Answer 1 · answered Apr 06 '09 at 10:04

3

You can use cascaded delete.

answered Apr 06 '09 at 10:04

daanish.rumani

317
1
5

How does that "create a list of tables"? – Tomalak Apr 06 '09 at 10:09
No it won't create a list, it will just *DO* what you wanted to do in the first place :D – Ian G Apr 06 '09 at 10:19
What if I wanted to create a list? :-D – Tomalak Apr 06 '09 at 10:26
This will not work, since what I want is to create a list so the user can determine weather to delete the data or move it to another user, or simply mark the user as inactive instead of deleting it. Doing a on cascade delete (or even set null) would be VERY bad in my app. – devzero Apr 06 '09 at 12:36

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

Not exactly sure of what you're going for here..

If you want a list of foreign keys referencing a table try this question How can I list all foreign keys referencing a given table in SQL Server?

edited May 23 '17 at 12:07

Community

1
1

answered Apr 06 '09 at 12:13

Gishu

134,492
47
225
308

It's pretty much what I want, except I need to do a loop after this query to check what tables/fk's actually contains data for a given user. I guess the simplest way of doing that is by using .net to loop through the result set and generate a query for each. – devzero Apr 06 '09 at 12:34

score 2 · Answer 3 · edited Nov 13 '18 at 23:43

2

Have you tried this one http://web.archive.org/web/20080511142245/http://sqlserver2000.databases.aspfaq.com:80/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html

edited Nov 13 '18 at 23:43

Community

1
1

answered Apr 06 '09 at 10:17

Ian G

29,468
21
78
92

score 2 · Answer 4 · answered Apr 06 '09 at 12:37

SQL 2005+ provides a number of system views like sys.tables and sys.foreign_key_columns which may help you.

SELECT
  pt.name AS ParentTable,
  pc.name AS ParentColumn,
  rt.name AS ReferencedTable,
  rc.name AS ReferencedColumn
FROM sys.foreign_key_columns fkc
  INNER JOIN sys.tables pt ON pt.object_id = fkc.parent_object_id
  INNER JOIN sys.columns pc ON pc.column_id = fkc.parent_column_id AND
                               pc.object_id = fkc.parent_object_id
  INNER JOIN sys.tables rt ON rt.object_id = fkc.referenced_object_id
  INNER JOIN sys.columns rc ON rc.column_id = fkc.referenced_column_id AND
                               rc.object_id = fkc.referenced_object_id

score 1 · Answer 5 · answered Apr 06 '09 at 12:35

this will list all tables where your ID exists:

DECLARE @Query        varchar(1000)
DECLARE @MaxRow       int
DECLARE @CurrentRow   int
DECLARE @CurrentTable varchar(500)
DECLARE @UserID       int

SET @UserID=???

CREATE TABLE #Rows
(
     RowID                int not null primary key identity(1,1)
    ,TableWithForeignKey  varchar(500)
)


CREATE TABLE #Temp
(
    RowValue  int
)

INSERT INTO #Rows
    (TableWithForeignKey )
    select
        t.name as TableWithForeignKey  --, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn 
        from sys.foreign_key_columns as fk
            inner join sys.tables    as t on fk.parent_object_id = t.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 fk.referenced_object_id = (select object_id from sys.tables where name = '????????')
        --order by TableWithForeignKey, FK_PartNo
SELECT @MaxRow=@@ROWCOUNT
SET @CurrentRow=0
SELECT * FROM #Rows
WHILE @CurrentRow<@MaxRow
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT @CurrentTable=TableWithForeignKey FROM #Rows WHERE RowID=@CurrentRow
    --SET @Query='DELETE FROM '+@CurrentTable+' WHERE UserID='+CONVERT(varchar(20),@UserID)
    SET @Query='SELECT COUNT(*) FROM '+@CurrentTable+' WHERE YourIDhere='+CONVERT(varchar(20),@UserID)
    PRINT @Query
    INSERT INTO #Temp
    EXECUTE (@Query)

    IF NOT EXISTS (SELECT RowValue FROM #Temp WHERE RowValue>0)
    BEGIN
        PRINT 'no matches found'
        DELETE #Rows WHERE RowID=@CurrentRow
    END
    ELSE
    BEGIN
        PRINT 'matches found!!'
    END

    DELETE #Temp
END
--list all tables where the ID exists
SELECT * FROM #Rows

This is essentialy correct, but you need to put [] table names, and YourIDhere should be dynamic. — devzero, Apr 06 '09 at 12:44

score 0 · Answer 6 · answered Apr 06 '09 at 12:32

A couple of methods for finding the tables that reference your User table through foreign key relationships have been posted and here's another: http://www.sqlservercurry.com/2009/03/simple-stored-procedure-to-find-foreign.html

Once you get the list of tables, you're going to have to write the SQL to determine which tables have records for a specific user. There's no way around that. Since the queries will all look the same, you could paste the table list into Excel, use a formula to build the queries, then paste the queries back into SSMS.

Find what Foreign keys are in use

6 Answers6