CREATE PROCEDURE [dbo].[gmi_richiedente_data_retention_policy]
	@idrichiedente  int
AS
BEGIN
	SET NOCOUNT ON;

   DECLARE @tot INT = 0;
DECLARE @rcnt INT = 0;
DECLARE @fkkey NVARCHAR(50) = '';
DECLARE @fktable NVARCHAR(50) = '';
DECLARE @sql NVARCHAR(255) = '';

BEGIN   
DECLARE db_cursor CURSOR FOR 
SELECT FK.TABLE_NAME as fktable, FK.TABLE_NAME + '.' + CU.COLUMN_NAME as fkkey
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
    WHERE PK.TABLE_NAME = 'richiedente'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @fktable, @fkkey;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
	SET @sql = N'SELECT @orcnt = count(*) from ' + @fktable + ' where ' + @fkkey + '= @id';
	EXECUTE sp_executesql @sql, N'@id INT, @orcnt INT OUTPUT',@id = @idrichiedente,@orcnt = @rcnt OUTPUT;
	SET @tot = @tot + @rcnt;
    FETCH NEXT FROM db_cursor INTO @fktable, @fkkey; 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor
SELECT @tot as cnt;
END
END
