MSSQL DDL(Database Define Language)

[MSSQL Data Type 참고]

https://docs.microsoft.com/ko-kr/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017

Site 참고

 

Data Definition Language

 1. 데이터베이스 객체 생성(CREATE)

CREATE TABLE Company_CODE ( Company_CODE INT , CompanyID VARCHAR(10) , ComapnyName VARCHAR(20) , ComapnySite VARCHAR(20) , CreateDate DATETIME )

  

 2. 데이터베이스 객체 수정(ALTER)

ALTER TABLE Company_CODE ALTER COLUMN Company_CODE INT NOT NULL GO    ALTER TABLE Company_CODE ADD Local VARCHAR(50) GO SELECT * FROM Company_CODE GO    ALTER TABLE Company_CODE ADD TotalMemberCount INT  DEFAULT 0 NOT NULL GO    sp_rename 'Member.TotalMemberCount ,TotalMemberCnt ,'COLUMN' --// sp_rename '테이블명.컬럼명','컬럼명','COLUMN'  sp_rename(Transact-SQL): http://msdn.microsoft.com/ko-kr/library/ms188351.aspx

 

 3. 데이터베이스 객체 삭제(DROP) 

ALTER TABLE Company_CODE DROP COLUMN Local GO      ALTER TABLE Company_CODE DROP COLUMN TotalMemberCnt GO 개체 'DF__Company__TotalMemberCnt __124B482B'은(는) 열 'TotalMemberCnt'에 종속되어 있습니다. -- default 제약 조건에 포함이 되어있기 때문에 먼저 삭제를 해야 삭제가 가능   ALTER TABLE Company_CODE DROP CONSTRAINT DF__Company__TotalMemberCnt __124B482B GO   ALTER TABLE Company_CODE DROP COLUMN TotalMemberCnt GO

 

 4. 데이터베이스 이름 변경(RENAME)

sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name' USE master;  GO  CREATE DATABASE Accounting;  GO  EXEC sp_renamedb N'Accounting', N'Financial';  GO  SELECT name, database_id, modified_date  FROM sys.databases  WHERE name = N'Financial';  GO   sp_renamedb(Transact-SQL): https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-renamedb-transact-sql?view=sql-server-2017

 

 5. 테이블 초기화(TRUNCATE)

TRUNCATE TABLE Company_CODE

TRUNCATE :

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017