Counting the number of links to an entry in the table via Foreign Keys

It was necessary for own purposes to solve the following problem - for one label (File) for each record, automatically calculate the number of external records connected via foreign key. The problem was solved for the specific structure of the File table, but if desired, the solution can be converted to a more universal one.

I will make a reservation that the solution was written for an unloaded database, without millions of rows of records and every minute updates, so there was no question of subsiding performance.

The main reason was that the number of external links to the File table may change during the development process and constantly reworking the query would be simply unwise. Some kind of modularity was planned in the system itself, therefore all final tables are not known exactly.

Script for creating two tablets:
')

CREATE TABLE [dbo].[File]( [IdFile] [int] IDENTITY(1, 1) NOT NULL, [NameFile] [nvarchar](max) NOT NULL, [CountUsage] [int] NOT NULL, PRIMARY KEY (IdFile) ) SET identity_insert [dbo].[File] ON; INSERT INTO [dbo].[File] ([IdFile], [NameFile],[CountUsage]) VALUES (1, 'test1', 0), (2, 'test2', 1000) SET identity_insert [dbo].[File] OFF; CREATE TABLE [dbo].[TestForFiles]( [IdTest] [int] IDENTITY(1, 1) NOT NULL, [IdFileForTest] [int] NOT NULL, PRIMARY KEY (IdTest) ) ALTER TABLE [dbo].[TestForFiles] WITH CHECK ADD CONSTRAINT [FK_TestForFiles_File] FOREIGN KEY([IdFileForTest]) REFERENCES [dbo].[File] ([IdFile]) ALTER TABLE [dbo].[TestForFiles] CHECK CONSTRAINT [FK_TestForFiles_File] INSERT INTO [dbo].[TestForFiles] ([IdFileForTest]) VALUES (1), (1), (1), (2) 

Get the File and TestForFiles tables. The TestForFiles table refers to File by the IdFileForTest field.

It turns out the following data set:



The following script generates a query to count the number of records in a table:

 DECLARE @sql_tables nvarchar(max) = null; SELECT @sql_tables = CASE WHEN @sql_tables IS NULL THEN '' ELSE @sql_tables + CHAR(13) + CHAR(10) + ' UNION ALL' + CHAR(13) + CHAR(10) END + ' SELECT ' + c.name + ' AS IdFile, count(*) AS FileCount FROM ' + t.name + ' GROUP BY ' + c.name 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 INNER JOIN sys.columns AS c2 ON fk.referenced_object_id = c2.object_id AND fk.referenced_column_id = c2.column_id WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables WHERE name = 'File') AND c2.name = 'IdFile'; IF @sql_tables IS NOT NULL BEGIN DECLARE @sql nvarchar(max) = 'UPDATE dbo.[File]' + CHAR(13) + CHAR(10) + 'SET CountUsage = t2.FileCount' + CHAR(13) + CHAR(10) + 'FROM dbo.[File]' + CHAR(13) + CHAR(10) + 'INNER JOIN (' + CHAR(13) + CHAR(10) + ' SELECT IdFile, SUM(FileCount) AS FileCount ' + CHAR(13) + CHAR(10) + ' FROM (' + CHAR(13) + CHAR(10) + @sql_tables + CHAR(13) + CHAR(10) + ' ) t' + CHAR(13) + CHAR(10) + ' GROUP BY IdFile' + CHAR(13) + CHAR(10) + ') t2 ON t2.IdFile = dbo.[File].IdFile'; print @sql; EXEC sp_executesql @sql; END; 

This request is generated here:

 UPDATE dbo.[File] SET CountUsage = t2.FileCount FROM dbo.[File] INNER JOIN ( SELECT IdFile, SUM(FileCount) AS FileCount FROM ( SELECT IdFileForTest AS IdFile, count(*) AS FileCount FROM TestForFiles GROUP BY IdFileForTest ) t GROUP BY IdFile ) t2 ON t2.IdFile = dbo.[File].IdFile 

As a result, we have the following table contents:



Once again, the task was solved for a specific File table, the counting works only for cases when there are foreign keys in the IdFile field.

Thank you in advance for your criticism.