166 lines
4.6 KiB
Transact-SQL
166 lines
4.6 KiB
Transact-SQL
-- =============================================
|
|
-- 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
|
|
-- Modifier: Zalán Máriusz
|
|
-- Date: 2020.03.06
|
|
-- Tanév paraméter kiegészítés
|
|
-- =============================================
|
|
DROP PROCEDURE IF EXISTS sp_GetEntitasAktivKapcsolatai
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetEntitasAktivKapcsolatai
|
|
@EntitasIDk AS XML -- a törlésre kijelölt entitás ID-k
|
|
,@EntitasNevek as XML -- a törlésre kijelölt entitások
|
|
,@tanevId int = null
|
|
AS
|
|
BEGIN
|
|
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT OFF;
|
|
|
|
/*declare @cucc xml
|
|
set @cucc = '<EntitasNevek><Entitas>OrarendiOra</Entitas><Entitas>Tanar</Entitas></EntitasNevek>'*/
|
|
|
|
-- XML paraméter értelmezése
|
|
|
|
CREATE TABLE #tempEntitasNevek (
|
|
EntitasNev varchar(255)
|
|
)
|
|
CREATE TABLE #tempentitasIdk (
|
|
entitasId int
|
|
)
|
|
CREATE TABLE #tempConnections (
|
|
ID int
|
|
,Target_Table varchar(255)
|
|
,Target_Column varchar(255)
|
|
,Source_Table varchar(255)
|
|
,Source_Column varchar(255)
|
|
)
|
|
DECLARE @tanevColname nvarchar(128)
|
|
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)
|
|
|
|
CREATE TABLE #result(
|
|
entitasId int
|
|
,targetTableName nvarchar(255)
|
|
,targetColumnName nvarchar(255)
|
|
,cnt INT
|
|
)
|
|
|
|
INSERT INTO #result (
|
|
entitasId
|
|
,cnt
|
|
) SELECT
|
|
entitasId
|
|
,0
|
|
FROM #tempentitasIdk
|
|
|
|
DECLARE
|
|
@sourceTable nvarchar(255)
|
|
,@sourceColumn nvarchar(255)
|
|
,@targetTable nvarchar(255)
|
|
,@targetColumn nvarchar(255)
|
|
,@tableName nvarchar(255)
|
|
|
|
CREATE TABLE #References (
|
|
PKTABLE_QUALIFIER nvarchar(255)
|
|
,PKTABLE_OWNER nvarchar(255)
|
|
,PKTABLE_NAME nvarchar(255)
|
|
,PKCOLUMN_NAME nvarchar(255)
|
|
,FKTABLE_QUALIFIER nvarchar(255)
|
|
,FKTABLE_OWNER nvarchar(255)
|
|
,FKTABLE_NAME nvarchar(255)
|
|
,FKCOLUMN_NAME nvarchar(255)
|
|
,KEY_SEQ int
|
|
,UPDATE_RULE int
|
|
,DELETE_RULE int
|
|
,FK_NAME nvarchar(255)
|
|
,PK_NAME nvarchar(255)
|
|
,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)
|
|
IF @tanevId IS NOT NULL BEGIN
|
|
SELECT
|
|
@tanevColname = c.name
|
|
FROM sys.columns c
|
|
INNER JOIN sys.tables t ON t.object_id = c.object_id ANd t.name = @targetTable
|
|
WHERE c.name in ('C_TANEVID','C_ALTANEVID')
|
|
END
|
|
|
|
|
|
SET @sql = N'
|
|
SELECT #tempentitasIdk.entitasId, '''+@targetTable+''','''+@targetColumn+''', COUNT(1) AS cnt
|
|
FROM '+@targetTable+'
|
|
INNER JOIN #tempentitasIdk ON #tempentitasIdk.entitasId = '+@targetTable+'.'+@targetColumn+'
|
|
WHERE 1=1 '
|
|
|
|
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @targetTable AND COLUMN_NAME = 'TOROLT') BEGIN
|
|
SET @sql = @sql + N'
|
|
AND ' + @targetTable + '.TOROLT = ''F'''
|
|
END
|
|
IF @tanevColname IS NOT NULL BEGIN
|
|
SET @sql += ' AND '+@targetTable+'.'+@tanevColname+' = @tanevId'
|
|
END
|
|
|
|
SET @sql = @sql + N'
|
|
GROUP BY #tempentitasIdk.entitasId'
|
|
|
|
INSERT INTO #result
|
|
EXEC sp_executesql @sql,N'@tanevId int',@tanevId = @tanevId
|
|
|
|
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
|