SQL Server master 데이터베이스 정리
· Version : SQL Server
SQL Server에서 master 데이터베이스의 역할은 시스템에 대한 모든 정보를 기록한다. 여기에는 로그인 계정, 끝점, 연결된 서버 및 시스템 구성설정과 같은 인스턴스 차원의 메타 데이터가 포함된다.
· master database : https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017
우리는 가끔 의도하지 않게 master 데이터베이스에 개체를 생성하는 경우가 있다. 대부분 개체를 생성할 때 USE 문을 생략했을 수도 있고 수 많은 작업창을 띄어 놓고 사용하다가 실수할 수도 있다. 이번 아티클에서는 master 데이터베이스를 정리하는 방법에 대해서 알아본다. master 데이터베이스는 시스템의 설정을 저장하고 있으므로 해당 작업시 주의가 필요 하다.
[master에서 삭제하지 않을 목록]
아래 표에 정으되어 있는 오브젝트 유형은 시스템과 관련있기 때문에 삭제 목록에서 제외한다.
|
ET
|
External table
|
|
IT
|
Internal table
|
|
PC
|
Assembly (CLR) stored procedure
|
|
PG
|
Plan guide
|
|
RF
|
Replication-filter-procedure
|
|
S
|
System table
|
|
SQ
|
Service queue
|
|
TA
|
Assembly (CLR) DML Trigger
|
|
X
|
Extended procedure
|
|
-
|
CLR User-Defined Data Type (UDDT)
|
사용자는 자신만의 모듈을 생성하여 시스템 객체로 표시할 수도 있다. 이 경우 시스템 저장프로시저와 구별 할 수 있는 확실한 방법이 없기 때문에 주관적으로 식별해야 한다.
[master 에서 삭제할 목록]
오브젝트를 삭제하기 위해서는 약간의 순서가 필요하다. 예를 들어 테이블을 삭제하려면 외부키나 뷰를 제거해야하며, 테이블 함수를 사용하는 테이블을 삭제 될 때까지 파티션 함수를 제거할 수 없다. 또한 순환 참조가 있을 수 있으므로 많은 경우 스크립트를 여러번 실행해야 할 수도 있다.
아래 표시된 유형은 객체를 삭제하면 자동으로 삭제되기 때문에 크게 걱정할 필요가 없다.
|
C
|
Check constraint
|
|
D
|
Default constraint
|
|
PK
|
Primary key constraint
|
|
TR
|
DML trigger
|
|
UQ
|
Unique constraint
|
나머지는 아래 순서대로 삭제해야한다.
|
1
|
F
|
Foreign key constraint
|
Need to be dropped before tables, as described below.
|
|
2
|
V
|
View
|
Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.
|
|
FN
|
SQL scalar function
|
|
IF
|
SQL inline table-valued function
|
|
TF
|
SQL table-valued-function
|
|
3
|
P
|
Stored procedure
|
Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).
|
|
4
|
U
|
Table (user-defined)
|
Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.
|
|
5
|
TT
|
Table type
|
Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.
|
|
Alias types
|
|
|
6
|
SO
|
Sequence object
|
Dropped after tables/modules because they can't be dropped if any table or module references them.
|
|
R
|
Rule (old-style, stand-alone)
|
|
D
|
Old-style CREATE DEFAULT
|
|
Partition functions
|
|
|
7
|
Partition schemes
|
|
Can't be dropped until after partition functions.
|
|
8
|
SN
|
Synonym
|
I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).
|
|
9
|
Schemas
|
|
I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.
|
|
10
|
Roles and Users
|
|
For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas.
(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.)
For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.
|
[기본 접근]
순환 참조는 여러 개체가 서로를 참조하므로 개체를 삭제하는 올바른 순서를 결정하기가 복잡하다. 그래서 스크립트가 실패하더라도 다시 실행하여 스크립트가 성공할때 까지 반복해서 실행 할 수 있는 스크립트로 작성해야 한다. 예를 들어 두 개의 SCHEMABINDING 뷰를 사용하는 경우 두 개의 뷰를 모두 삭제해야 테이블이 삭제된다. 뷰 순서가 명확하다면 뷰 순서대로 하면 되지만 뷰가 여러개 이면서 서로 뷰를 참조하는 경우 순서를 예측하기 어렵기 때문에 뷰 삭제 명령을 반복해서 실행한다.
|
try { drop view 1; go; drop view 2; }
|
[삭제 스크립트 (순서대로 실행)]
Foreign keys
|
-- script to drop Foreign Keys
USE [master];
GO
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
ALTER TABLE ' + objectname + N'
DROP CONSTRAINT ' + fkname + N';
END TRY
BEGIN CATCH
SELECT N''FK ' + fkname + N' failed. Run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT fkname = QUOTENAME(fk.[name]),
objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS t
ON fk.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
) AS src;
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
View and functions
|
-- script to drop views
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
DROP VIEW ' + objectname + N';
END TRY
BEGIN CATCH
SELECT N''View ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE v.is_ms_shipped = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to drop functions
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Function ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
AND o.[type] IN ('FN','IF','TF')
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Stored Procedures
|
-- script to drop procedures
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Procedure ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'P'
AND o.is_ms_shipped = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Tables
|
-- script to drop user tables
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Table ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.tables AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Table Types
|
-- script to drop table types
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Type ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.types AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_table_type = 1
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Alias Types
|
-- script to drop alias types
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Type ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.types AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.user_type_id > 256
AND o.is_table_type = 0
AND o.is_assembly_type = 0 -- not a CLR UDT
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Sequences
|
-- script to drop sequences
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Sequence ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.sequences AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Old-style CREATE RULE
|
-- script to drop old-style Rules
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Rule ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
AND [type] = 'R'
AND parent_object_id = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Old-style CREATE DEFAULT
|
-- script to drop old-style Defaults
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Default ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
AND [type] = 'D'
AND parent_object_id = 0
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Partition functions
|
-- script to drop partition functions
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Partition function ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME([name])
FROM sys.partition_functions
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Partition schemes
|
-- script to drop partition schemes
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME([name])
FROM sys.partition_schemes
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Synonyms
|
-- script to drop synonyms
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Synonym ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
FROM sys.synonyms AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Schemas
|
-- script to drop schemas
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';
END TRY
BEGIN CATCH
SELECT N''Schema ' + objectname + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
' FROM
(
SELECT QUOTENAME(s.[name])
FROM sys.schemas AS s
WHERE [schema_id] BETWEEN 5 AND 16383
) AS src(objectname);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to re-route default schemas
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;
END TRY
BEGIN CATCH
SELECT N''User ' + p.name + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
'
FROM sys.database_principals AS p
WHERE p.default_schema_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM sys.schemas AS s
WHERE name = p.default_schema_name
);
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
Roles and Users
|
-- script to change ownership of roles to dbo
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;
END TRY
BEGIN CATCH
SELECT N''Role ' + r.name + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
'
FROM sys.database_principals AS r
INNER JOIN sys.database_principals AS u
ON r.owning_principal_id = u.principal_id
WHERE r.[type] = 'R'
AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!
AND r.is_fixed_role = 0;
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to change ownership of schemas to dbo
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;
END TRY
BEGIN CATCH
SELECT N''Schema ' + name + N' failed - run the script again.'',
ERROR_MESSAGE(); END CATCH
'
FROM sys.schemas
WHERE [schema_id] BETWEEN 5 AND 16383
AND principal_id BETWEEN 5 AND 16383;
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to remove members from roles
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
' + CASE WHEN CONVERT(tinyint,
PARSENAME(CONVERT(nvarchar(128),
SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+
THEN
N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '
+ QUOTENAME(m.name) + N';'
ELSE
N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name
+ ''', @membername = N''' + m.name + N''';'
END + N'
END TRY
BEGIN CATCH
SELECT N''Role ' + r.name + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
'
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS r
ON rm.role_principal_id = r.principal_id
AND r.is_fixed_role = 0
INNER JOIN sys.database_principals AS m
ON rm.member_principal_id = m.principal_id;
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to drop roles
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
DROP ROLE ' + QUOTENAME(r.name) + N';
END TRY
BEGIN CATCH
SELECT N''Role ' + r.name + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
'
FROM sys.database_principals AS r
WHERE r.[type] = 'R'
AND r.name <> N'public'
AND r.is_fixed_role = 0;
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
|
-- script to drop users
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'BEGIN TRY
DROP USER ' + QUOTENAME(u.name) + N';
END TRY
BEGIN CATCH
SELECT N''User ' + u.name + N' failed - run the script again.'',
ERROR_MESSAGE();
END CATCH
'
FROM sys.database_principals AS u
WHERE u.[type] IN ('U','S')
AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND u.name NOT LIKE N'##%##';
SELECT @sql;
--EXEC sys.sp_executesql @sql;
|
지금까지 master 데이터베이스를 정리하는 작업 및 순서, 스크립트 생성에 대해서 알아보았다. 객체를 삭제하는 명령은 자동화 할 수 있지만 모든것은 완벽히 분석할 수는 없다. (사용자 모듈을 시스템 모듈로 생성한 경우 등) 위 스크립트를 사용하더라도 생성된 스크립트를 반드시 DBA 가 검토를 해야한다. master 데이터베이스를 정리하는것은 위험한 작업이므로 반드시 많은 테스트와 검증이 필요하다.
[참고자료]
https://www.mssqltips.com/sqlservertip/4530/cleaning-up-the-sql-server-master-database/
2019-04-18 / Sungwook Kang / http://sungwookkang.com
SQL Server, SSMS, mssql, DBA, master database, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions, sys.partition_schemes, sys.procedures, sys.schemas, sys.sequences,
