76 lines
2.3 KiB
Transact-SQL
76 lines
2.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspCreateSchemaSPFN
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspCreateSchemaSPFN
|
|
@pRoutinList nvarchar(max) -- Vesszővel elválasztott SP és FN lista (akár vegyesen is), NULL esetén az összeset frissíti
|
|
,@pSchemaName nvarchar(max) = NULL -- Schema neve, amit frissíteni kell, NULL esetén az összes schemán végigmegy
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
|
|
DECLARE
|
|
@spList nvarchar(max)
|
|
,@fnList nvarchar(max)
|
|
,@notExistingObject nvarchar(max)
|
|
,@dbName nvarchar(80)
|
|
|
|
DECLARE @Routine TABLE (
|
|
Id int IDENTITY(1, 1)
|
|
,RoutineName nvarchar(255)
|
|
)
|
|
|
|
IF @pRoutinList IS NULL BEGIN
|
|
EXEC dev.uspCreateSchemaFunctions @pSchemaName, NULL
|
|
EXEC dev.uspCreateSchemaStoredProcedures @pSchemaName, NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO @Routine (RoutineName)
|
|
SELECT LTRIM(RTRIM(s.value))
|
|
FROM STRING_SPLIT (@pRoutinList, N',') s
|
|
|
|
SET @notExistingObject = NULL
|
|
SELECT @notExistingObject = ISNULL(@notExistingObject + ', ', '') + r.RoutineName
|
|
FROM @Routine r
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM sys.all_objects p
|
|
WHERE r.RoutineName COLLATE HUNGARIAN_CI_AS = p.name COLLATE HUNGARIAN_CI_AS
|
|
AND p.schema_id = 1
|
|
AND p.type IN ('P', 'FN', 'IF', 'TF') )
|
|
|
|
IF @notExistingObject IS NOT NULL BEGIN
|
|
SET @dbName = DB_NAME()
|
|
RAISERROR('A parameterekben megadott SP/FN-ek kozul a kovettkezok nem leteznek a %s adatbazisban: %s', 16, 1, @dbName, @notExistingObject)
|
|
RETURN
|
|
END
|
|
|
|
SET @fnList = (
|
|
SELECT r.ROUTINE_NAME AS "FunctionName"
|
|
FROM INFORMATION_SCHEMA.ROUTINES r
|
|
INNER JOIN @Routine s ON RoutineName = r.ROUTINE_NAME
|
|
WHERE r.ROUTINE_SCHEMA = 'dbo'
|
|
AND r.ROUTINE_TYPE = 'FUNCTION'
|
|
ORDER BY s.Id
|
|
FOR XML PATH(''), ROOT('FunctionNames')
|
|
)
|
|
|
|
SET @spList = (
|
|
SELECT r.ROUTINE_NAME AS "StoredProcedureName"
|
|
FROM INFORMATION_SCHEMA.ROUTINES r
|
|
INNER JOIN @Routine s ON RoutineName = r.ROUTINE_NAME
|
|
WHERE r.ROUTINE_SCHEMA = 'dbo'
|
|
AND r.ROUTINE_TYPE = 'PROCEDURE'
|
|
ORDER BY s.Id
|
|
FOR XML PATH(''), ROOT('StoredProcedureNames')
|
|
)
|
|
|
|
IF @fnList IS NOT NULL BEGIN
|
|
EXEC dev.uspCreateSchemaFunctions @pSchemaName, @fnList
|
|
END
|
|
IF @spList IS NOT NULL BEGIN
|
|
EXEC dev.uspCreateSchemaStoredProcedures @pSchemaName, @spList
|
|
END
|
|
END
|
|
GO
|
|
|