158 lines
4.1 KiB
Transact-SQL
158 lines
4.1 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: Telek Ákos
|
|
-- Create date: 2016.07.20.
|
|
-- Description: Entitás törléséhez lekérdezi az aktív kapcsolatait. Van-e olyan kapcsolódása, ahol vannak nem törölt rekordok. Visszaadja
|
|
-- =============================================
|
|
IF OBJECT_ID('sp_GetEntitasAktivKapcsolatai') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetEntitasAktivKapcsolatai
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetEntitasAktivKapcsolatai
|
|
-- Add the parameters for the stored procedure here
|
|
@EntitasIDk as xml, -- a törlésre kijelölt entitás ID-k
|
|
@EntitasNevek as xml -- a törlésre kijelölt entitások
|
|
AS
|
|
BEGIN
|
|
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @tempEntitasNevek TABLE (
|
|
EntitasNev varchar(50)
|
|
)
|
|
CREATE TABLE #tempentitasIdk (
|
|
entitasId int
|
|
)
|
|
|
|
DECLARE @tempConnections TABLE (
|
|
ID int,
|
|
Target_Table varchar(50),
|
|
Target_Column varchar(50),
|
|
Source_Table varchar(50),
|
|
Source_Column varchar(50)
|
|
)
|
|
|
|
INSERT INTO @tempEntitasNevek
|
|
SELECT DISTINCT EntitasNev = 'T_' + Entitas.value('(.)[1]', 'varchar(50)')
|
|
FROM @EntitasNevek.nodes('EntitasNevek/Entitas') as EntitasNevek(Entitas)
|
|
|
|
INSERT INTO #tempentitasIdk
|
|
SELECT DISTINCT entitasId = entitasId.value('(.)[1]', 'int')
|
|
FROM @entitasIdk.nodes('Entitasok/EntitasId') as Entitasok(entitasId)
|
|
|
|
DECLARE @result TABLE (
|
|
entitasId INT,
|
|
targetTableName nvarchar(100),
|
|
targetColumnName nvarchar(100),
|
|
cnt INT
|
|
)
|
|
|
|
INSERT INTO @result (
|
|
entitasId,
|
|
cnt
|
|
)
|
|
|
|
SELECT entitasId, 0
|
|
FROM #tempentitasIdk
|
|
|
|
DECLARE
|
|
@sourceTable nvarchar(30),
|
|
@sourceColumn nvarchar(30),
|
|
@targetTable nvarchar(30),
|
|
@targetColumn nvarchar(30),
|
|
@tableName nvarchar(30)
|
|
|
|
DECLARE @References TABLE (
|
|
PKTABLE_QUALIFIER nvarchar(50),
|
|
PKTABLE_OWNER nvarchar(50),
|
|
PKTABLE_NAME nvarchar(50),
|
|
PKCOLUMN_NAME nvarchar(50),
|
|
FKTABLE_QUALIFIER nvarchar(50),
|
|
FKTABLE_OWNER nvarchar(50),
|
|
FKTABLE_NAME nvarchar(50),
|
|
FKCOLUMN_NAME nvarchar(50),
|
|
KEY_SEQ int,
|
|
UPDATE_RULE int,
|
|
DELETE_RULE int,
|
|
FK_NAME nvarchar(50),
|
|
PK_NAME nvarchar(50),
|
|
DEFERRABILITY int
|
|
);
|
|
|
|
DECLARE table_cur CURSOR LOCAL FOR
|
|
SELECT EntitasNev FROM @tempEntitasNevek
|
|
|
|
OPEN table_cur
|
|
|
|
FETCH NEXT FROM table_cur
|
|
INTO @tableName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
INSERT INTO @References
|
|
EXEC sp_fkeys @tableName, 'dbo'
|
|
|
|
FETCH NEXT FROM table_cur
|
|
INTO @tableName
|
|
|
|
END
|
|
CLOSE table_cur
|
|
DEALLOCATE table_cur
|
|
|
|
|
|
DECLARE ref_cur CURSOR LOCAL FOR
|
|
SELECT PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME
|
|
FROM @references WHERE FKCOLUMN_NAME != 'ID' --Azért szűrjük az ID mezőt, mert a leszármazott tábláknál van rá referencia
|
|
|
|
OPEN ref_cur
|
|
|
|
FETCH NEXT FROM ref_cur
|
|
INTO @sourceTable, @sourceColumn, @targetTable, @targetColumn
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
DECLARE @sql nvarchar(1000), @FINALQUERY nvarchar(1000)
|
|
|
|
SET @targetTable += '_OSSZES'
|
|
SET @sourceTable += '_OSSZES'
|
|
|
|
SET @sql = N'
|
|
SELECT #tempentitasIdk.entitasId, ''' + @targetTable + ''',''' + @targetColumn + ''', COUNT(1) AS cnt
|
|
FROM '+@targetTable+'
|
|
INNER JOIN #tempentitasIdk ON #tempentitasIdk.entitasId = ' + @targetTable + '.' + @targetColumn
|
|
|
|
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @targetTable AND COLUMN_NAME = 'TOROLT') BEGIN
|
|
SET @sql = @sql + N'
|
|
WHERE ' + @targetTable + '.TOROLT = ''F'''
|
|
END
|
|
|
|
SET @sql = @sql + N'
|
|
GROUP BY #tempentitasIdk.entitasId'
|
|
|
|
INSERT INTO @result
|
|
EXEC sp_executesql @sql
|
|
|
|
FETCH NEXT FROM ref_cur
|
|
INTO @sourceTable, @sourceColumn, @targetTable, @targetColumn
|
|
|
|
END
|
|
|
|
CLOSE ref_cur
|
|
DEALLOCATE ref_cur
|
|
|
|
IF OBJECT_ID('tempdb..#tempentitasIdk') IS NOT NULL BEGIN
|
|
DROP TABLE #tempentitasIdk
|
|
END
|
|
|
|
SELECT entitasId, targetTableName, targetColumnName, SUM(cnt) SUMROWCOUNT
|
|
FROM @result
|
|
GROUP BY entitasId, targetTableName, targetColumnName
|
|
HAVING SUM(cnt) > 0
|
|
END
|
|
GO
|