131 lines
No EOL
4.9 KiB
Transact-SQL
131 lines
No EOL
4.9 KiB
Transact-SQL
-- ============================================================================================
|
|
-- Schema view-kat generál a paraméterekben megadott táblákhoz és schemákhoz,
|
|
-- illetve, ha léteznek akkor frissíti őket.
|
|
--
|
|
-- Ha a @pTables vagy a @pSchemas paraméter értéke NULL, akkor az összes
|
|
-- táblára/schemára megcsinálja a view-kat
|
|
|
|
-- @pTables: tábla nevek vesszővel elválasztva, pl.: 'T_TANEV, T_UJTABLA,T_TANULO'
|
|
-- @pSchemas: schema nevek vesszővel elválasztva, pl.: 'KR_asd_Schema,dbo, KR_dobokata_Schema'
|
|
-- ============================================================================================
|
|
DROP PROCEDURE IF EXISTS dev.uspCreateSchemaViews
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspCreateSchemaViews
|
|
@pTables nvarchar(max)
|
|
,@pSchemas nvarchar(max) = NULL
|
|
,@pDebugMode bit = 0
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@tableName sysname
|
|
,@schemaName sysname
|
|
,@intezmenyId int
|
|
,@tanevId int
|
|
,@notExistingObject sysname
|
|
,@tanevColumn sysname
|
|
,@intezmenyColumn sysname
|
|
,@toroltColumn sysname
|
|
,@dbName sysname = DB_NAME()
|
|
,@sql nvarchar(max)
|
|
|
|
SELECT TOP 1 @notExistingObject = s.value
|
|
FROM STRING_SPLIT(@pTables, ',') s
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM sys.tables t
|
|
WHERE s.value = t.name
|
|
AND t.name LIKE 'T[_]%'
|
|
AND t.schema_id = 1
|
|
)
|
|
|
|
IF @notExistingObject IS NOT NULL BEGIN
|
|
RAISERROR('A megadott tábla nem létezik a %s adatbázisban: %s', 16, 1, @dbName, @notExistingObject)
|
|
RETURN
|
|
END
|
|
|
|
SELECT TOP 1 @notExistingObject = s.value
|
|
FROM STRING_SPLIT(@pSchemas, ',') s
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM sys.schemas t
|
|
WHERE s.value = t.name
|
|
)
|
|
|
|
IF @notExistingObject IS NOT NULL BEGIN
|
|
RAISERROR('A megadott schema nem létezik a %s adatbázisban: %s', 16, 1, @dbName, @notExistingObject)
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @ViewCursor CURSOR
|
|
|
|
SET @ViewCursor = CURSOR FOR
|
|
SELECT
|
|
tbl.name AS TableName
|
|
,sch.name AS SchemaName
|
|
,i.ID AS IntezmenyId
|
|
,tv.ID AS TanevId
|
|
,ctv.name AS TanevColumn
|
|
,cin.name AS IntezmenyColumn
|
|
,ctr.name AS ToroltColumn
|
|
FROM sys.tables tbl
|
|
LEFT JOIN sys.columns cid ON tbl.object_id = cid.object_id AND cid.name IN ('ID')
|
|
LEFT JOIN sys.columns cin ON tbl.object_id = cin.object_id AND cin.name IN ('C_INTEZMENYID', 'C_ALINTEZMENYID')
|
|
LEFT JOIN sys.columns ctv ON tbl.object_id = ctv.object_id AND ctv.name IN ('C_TANEVID', 'C_ALTANEVID')
|
|
LEFT JOIN sys.columns ctr ON tbl.object_id = ctr.object_id AND ctr.name IN ('TOROLT')
|
|
CROSS JOIN sys.schemas sch
|
|
INNER JOIN STRING_SPLIT(ISNULL(@pTables, ''), ',') t ON (@pTables IS NULL OR t.value = tbl.name)
|
|
INNER JOIN STRING_SPLIT(ISNULL(@pSchemas, ''), ',') s ON (@pSchemas IS NULL OR sch.name = s.value)
|
|
LEFT JOIN T_INTEZMENY i ON 'KR_' + i.C_AZONOSITO + '_Schema' = sch.name AND i.TOROLT = 'F'
|
|
LEFT JOIN T_TANEV tv ON tv.C_INTEZMENYID = i.ID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
WHERE (sch.name like 'KR[_]%[_]Schema' or sch.name = 'dbo')
|
|
AND tbl.schema_id = 1
|
|
AND tbl.name like 'T[_]%'
|
|
AND (tbl.name not like 'T[_]DKT[_]%' OR tbl.name IN ('T_DKT_FILE', 'T_DKT_FELADAT', 'T_DKT_FELADATFILE'))
|
|
AND tbl.name NOT IN ('T_ENTITYATTRIBUTEHISTORY', 'T_ENTITYHISTORY', 'T_ENTITYATTRIBUTEHISTORY_ARCHIVE', 'T_ENTITYBLOBVALUES', 'T_ENTITYBLOBVALUES_ARCHIVE')
|
|
ORDER BY sch.name, tbl.name
|
|
|
|
OPEN @ViewCursor
|
|
|
|
FETCH NEXT FROM @ViewCursor
|
|
INTO @tableName, @schemaName, @intezmenyId, @tanevId, @tanevColumn, @intezmenyColumn, @toroltColumn
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
-- _OSSZES view-ok
|
|
SET @sql = '
|
|
CREATE OR ALTER VIEW [' + @schemaName + '].[' + @tableName + '_OSSZES] AS
|
|
SELECT *
|
|
FROM dbo.' + @tableName + CHAR(13)
|
|
+ CASE WHEN @intezmenyColumn > '' AND @intezmenyId > 0 THEN CONCAT('WHERE ', @intezmenyColumn, ' = ', @intezmenyId, CHAR(13)) ELSE '' END
|
|
|
|
EXEC sp_executesql @sql
|
|
IF @pDebugMode = 1 OR @@ERROR <> 0 PRINT @schemaName + '.' + @tableName + '_OSSZES (osszes)'
|
|
IF @pDebugMode = 1 PRINT @sql
|
|
|
|
-- normál view-ok
|
|
SET @sql = '
|
|
CREATE OR ALTER VIEW [' + @schemaName + '].[' + @tableName + '] AS
|
|
SELECT *
|
|
FROM dbo.' + @tableName + CHAR(13)
|
|
+ 'WHERE '
|
|
+ CASE WHEN @intezmenyColumn > '' AND @intezmenyId > 0 THEN CONCAT(@intezmenyColumn, ' = ', @intezmenyId, CHAR(13), 'AND ') ELSE '' END
|
|
+ CASE WHEN @tanevColumn > '' AND @tanevId > 0 THEN CONCAT(@tanevColumn + ' = ', @tanevId, CHAR(13), 'AND ') ELSE '' END
|
|
+ CASE WHEN @toroltColumn > '' THEN 'TOROLT = ''F''' + CHAR(13) + 'AND ' ELSE '' END
|
|
|
|
IF RIGHT(@sql, 4) = 'AND ' SET @sql = LEFT(@sql, LEN(@sql) - 4)
|
|
IF RIGHT(@sql, 6) = 'WHERE ' SET @sql = LEFT(@sql, LEN(@sql) - 6)
|
|
IF @schemaName = 'dbo' SET @sql = null
|
|
|
|
EXEC sp_executesql @sql
|
|
IF @pDebugMode = 1 OR @@ERROR <> 0 PRINT @schemaName + '.' + @tableName + ' (normal)'
|
|
IF @pDebugMode = 1 PRINT @sql
|
|
|
|
FETCH NEXT FROM @ViewCursor
|
|
INTO @tableName, @schemaName, @intezmenyId, @tanevId, @tanevColumn, @intezmenyColumn, @toroltColumn
|
|
|
|
END
|
|
|
|
CLOSE @ViewCursor
|
|
|
|
GO |