'sys.partition_functions' 태그의 글 목록

sys.partition_functions

SQL Server master 데이터베이스 정리 2019.04.19

SQL Server master 데이터베이스 정리

2019. 4. 19. 01:47

DBA, master database, MSSQL, SQL Server, SSMS, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions

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.
5	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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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];

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,

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Agent Job에서 sysploicy_purge_history 작업실패 (0)	2019.08.08
SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 (0)	2019.05.03
SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18
SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11

PREV 1 NEXT

Data Science Lab