1

I need to return a count of a particular foreign key across all tables in a database given a table name, column name, and an id.

So if the passed in parameters are TableName, ColumnName, RecordID, I would need to look at all tables that have that column as a foreign key and return the count of all RecordID's that match.

I can see how to do it with a cursor and dynamic sql, but I'd like to figure out something a bit more elegant as it were.

I guess I really just need someone smarter than me to tell me you can or can't do it.

EDIT

Output should be something like:
Count of ID 1 in OtherTable is 5
Count of ID 1 in OtherTable2 is 10

So you could pass in any table name and record ID and have it return counts for each referenced table

EDIT 2. I think there should be a better answer than this, but here's what I came up with. It does use dynamic sql, but no cursors at least. (It uses adventureworks)

DECLARE @RecID AS INT = 1
DECLARE @TableName AS VARCHAR(255) = 'Product'

CREATE TABLE #temp
  (
     tablename   VARCHAR(255),
     ColumnName  VARCHAR(255),
     sqlStatment VARCHAR(max),
     IDCount     INT
  )

INSERT INTO #temp
            (tablename,
             ColumnName,
             sqlStatment)
SELECT Object_schema_name(f.parent_object_id)
       + '.' + Object_name(f.parent_object_id)            AS TableName,
       Col_name(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
       'update #Temp set IDCount = (select count(*) from '
       + Object_schema_name(f.parent_object_id)
       + '.' + Object_name(f.parent_object_id)
       + ' where '
       + Col_name(fc.parent_object_id, fc.parent_column_id)
       + ' = ' + CONVERT(VARCHAR, @RecID)
       + ') where tablename = '''
       + Object_schema_name(f.parent_object_id)
       + '.' + Object_name(f.parent_object_id) + ''';'
FROM   sys.foreign_keys AS f
       INNER JOIN sys.foreign_key_columns AS fc
               ON f.OBJECT_ID = fc.constraint_object_id
WHERE  Object_name(f.referenced_object_id) = @TableName

DECLARE @sql AS VARCHAR(max) = ''

SELECT @sql = @Sql + sqlStatment
FROM   #temp

EXEC (@sql)

SELECT *
FROM   #temp

DROP TABLE #temp
Dayton Brown
  • 1,228
  • 3
  • 16
  • 31
  • Yes, you can do it: Use the system tables: http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/ and a simple select statement – xQbert Feb 25 '15 at 21:08
  • 1
    Kudos for asking how to do something "without a cursor". ! – granadaCoder Feb 25 '15 at 21:55

2 Answers2

2

I think this is what you want :

SELECT COUNT(*)
FROM sys.foreign_key_columns AS fk
JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
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 = 'TableThatContainsTheKey');
Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
0

I hope this helps:

use yourdatabase
select count(*) from sysobjects where xtype='F'

(Edit: sorry, I meant 'F'. 'PK' is for primary keys.)

Laszlo T
  • 1,165
  • 10
  • 22