60 lines
1.6 KiB
Transact-SQL
60 lines
1.6 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspDropSchemaViews
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspDropSchemaViews
|
|
@pViewList 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
|
|
@sql nvarchar(max) = '';
|
|
|
|
IF @pSchemaName IS NULL AND @pViewList IS NULL BEGIN
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @View TABLE (
|
|
Id int IDENTITY(1, 1)
|
|
,ViewName nvarchar(255)
|
|
)
|
|
|
|
INSERT INTO @View (ViewName)
|
|
SELECT LTRIM(RTRIM(s.value))
|
|
FROM STRING_SPLIT (@pViewList, N',') s
|
|
UNION ALL
|
|
SELECT LTRIM(RTRIM(s.value)) + '_OSSZES'
|
|
FROM STRING_SPLIT (@pViewList, N',') s
|
|
|
|
|
|
IF @pSchemaName IS NULL BEGIN
|
|
|
|
SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10)
|
|
FROM sys.views v
|
|
INNER JOIN @View x ON x.ViewName COLLATE HUNGARIAN_CI_AS = v.name COLLATE HUNGARIAN_CI_AS
|
|
ORDER BY x.Id
|
|
END
|
|
|
|
IF @pViewList IS NULL BEGIN
|
|
|
|
SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10)
|
|
FROM sys.views v
|
|
WHERE SCHEMA_NAME(v.schema_id) = @pSchemaName
|
|
|
|
END
|
|
|
|
IF @pViewList IS NULL AND @pSchemaName IS NOT NULL BEGIN
|
|
|
|
SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10)
|
|
FROM sys.views v
|
|
INNER JOIN @View x ON x.ViewName COLLATE HUNGARIAN_CI_AS = v.name COLLATE HUNGARIAN_CI_AS
|
|
WHERE SCHEMA_NAME(v.schema_id) = @pSchemaName
|
|
|
|
END
|
|
|
|
EXEC sp_executesql @sql
|
|
|
|
END
|
|
GO
|
|
|