스키마와 명명 규약

SELECT s.name AS 스키마, COUNT(o.object_id) AS 객체수
FROM sys.schemas s
    LEFT JOIN sys.objects o ON o.schema_id = s.schema_id AND o.is_ms_shipped = 0
WHERE s.name NOT IN ('sys', 'INFORMATION_SCHEMA')
GROUP BY s.name
HAVING COUNT(o.object_id) > 0;

-- 같은 이름을 두 스키마에 둘 수 있습니다.
CREATE TABLE Board.LOGS  (num int IDENTITY PRIMARY KEY, msg nvarchar(100));
CREATE TABLE Member.LOGS (num int IDENTITY PRIMARY KEY, msg nvarchar(100));

SELECT s.name + '.' + t.name AS 표
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = 'LOGS';

SELECT COUNT(*) FROM POSTS;

CREATE USER board_user  WITHOUT LOGIN WITH DEFAULT_SCHEMA = Board;
CREATE USER member_user WITHOUT LOGIN WITH DEFAULT_SCHEMA = Member;
GRANT SELECT, INSERT ON SCHEMA::Board  TO board_user;
GRANT SELECT, INSERT ON SCHEMA::Member TO member_user;
GO

-- 두 사람이 글자 하나 다르지 않은 같은 문장을 실행합니다.
EXECUTE AS USER = 'board_user';
INSERT INTO LOGS (msg) VALUES (N'board_user 가 넣음');
REVERT;
GO
EXECUTE AS USER = 'member_user';
INSERT INTO LOGS (msg) VALUES (N'member_user 가 넣음');
REVERT;
GO

SELECT 'Board.LOGS' AS 표, msg FROM Board.LOGS
UNION ALL
SELECT 'Member.LOGS', msg FROM Member.LOGS;

CREATE USER reader WITHOUT LOGIN WITH DEFAULT_SCHEMA = Board;
GRANT SELECT ON SCHEMA::Board TO reader;
GO

-- 권한을 준 뒤에 표를 만듭니다.
CREATE TABLE Board.T_LATER (num int IDENTITY PRIMARY KEY, msg nvarchar(50));
INSERT INTO Board.T_LATER (msg) VALUES (N'나중에 만든 표');
GO

EXECUTE AS USER = 'reader';
SELECT msg AS 읽힘 FROM Board.T_LATER;   -- 권한을 따로 주지 않았습니다
SELECT COUNT(*) FROM Member.USERS;         -- 다른 스키마입니다
REVERT;

CREATE TABLE Board.T_MOVE (
    num int IDENTITY PRIMARY KEY,
    post_num int NOT NULL,
    CONSTRAINT FK_T_MOVE_POSTS FOREIGN KEY (post_num) REFERENCES Board.POSTS(num));
CREATE NONCLUSTERED INDEX IX_T_MOVE_post ON Board.T_MOVE (post_num);
GO

ALTER SCHEMA Member TRANSFER Board.T_MOVE;
GO

SELECT s.name + '.' + t.name AS 표,
       (SELECT COUNT(*) FROM sys.foreign_keys f WHERE f.parent_object_id = t.object_id) AS 외래키,
       (SELECT COUNT(*) FROM sys.indexes i WHERE i.object_id = t.object_id AND i.name IS NOT NULL) AS 인덱스
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = 'T_MOVE';

GRANT SELECT ON OBJECT::Board.T_PERM TO p_user;
GO
ALTER SCHEMA Member TRANSFER Board.T_PERM;
GO
SELECT COUNT(*) AS 남은권한수 FROM sys.database_permissions
WHERE major_id = OBJECT_ID('Member.T_PERM');

-- 여기까지 만든 것을 지웁니다. 다음 절의 세기가 어긋나지 않게 합니다.
DROP TABLE Board.LOGS, Member.LOGS, Board.T_LATER;
DROP USER board_user;
DROP USER member_user;
DROP USER reader;

SELECT LEFT(o.name, CHARINDEX('_', o.name + '_') - 1) AS 접두사,
       o.type_desc AS 종류, COUNT(*) AS 개수
FROM sys.objects o
WHERE o.is_ms_shipped = 0 AND o.type IN ('PK', 'UQ', 'F', 'C', 'D')
GROUP BY LEFT(o.name, CHARINDEX('_', o.name + '_') - 1), o.type_desc
ORDER BY o.type_desc;

SELECT COUNT(*) AS 글수 FROM board.posts;      -- 전부 소문자로 적어도 됩니다

-- 값을 비교할 때도 마찬가지입니다.
SELECT user_id, nickname FROM Member.USERS WHERE user_id = 'HONG';

CREATE TABLE Board.USER (num int);      -- USER 는 예약어입니다
GO
CREATE TABLE Board.[USER] (num int);    -- 대괄호로 감싸면 만들어집니다
GO
DROP TABLE Board.[USER];