羊蹄口丁的進化史

有分為DataBase、Table、Column、Data、Link Server、User的CRUD，Role、Type、Function、View、Stored Procedures等與其他較相似，如有發現比較特別的用法再來更新。

------------新增DataBase------------
Create DataBase [DATABASE]
ON( --建立資料庫實體設定
Name = DATABASE_dat,--資料庫名稱
FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\datebase_dat.mdf',--檔案實體位置
Size = 10,--起始大小(MB)
MaxSize = 50,--最大容量(MB)
FileGrowth = 5--每超過大小自動增長5%
)
LOG ON--建立交易檔實體設定
( Name = DATABASE_log,
  FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\database_log.ldf',
  Sizze = 5MB, --起始大小(MB)
  MaxSize = 25MB, --最大容量(MB)
  FileGrowth = 5MB ) ; --每超過大小自動增長5MB
--修改DataBase
Alter DataBase [DATABASE] Collate Latin1_General_100_CS_AS_SC;
--刪除DataBase
IF DB_ID('DATABASE') IS NOT NULL Drop DataBase [DATABASE];
--查詢DataBase
Select * From sys.databases;
--重新命名DataBase
ALTER DATABASE [DATABASE] Modify Name = NewDATABASE

 

--建立Table
Create Table [TABLE](pkName VARCHAR(MAX) DEFAULT '' Constraint PK_TABLE Primary Key([TABLE]) NOT NULL )
--修改Table
Alter Table [TABLE] Add pkName VARCHAR(255) Constraint DF_TABLE_pkName Default '' Constraint PK_TABLE_pkName Primary Key(pkName) NOT NULL

--刪除Table
IF OBJECT_ID('DATABASE.dbo.TABLE') IS NOT NULL Drop Table [TABLE]
--查詢Table
Select * From sys.tables

--重新命名Table
EXEC sp_rename 'DATABASE.dbo.TABLE','TABLE_New'

--對Table加上解釋
IF Not Exists(Select TOP 1 1 From ::fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', '[TABLE]', NULL, NULL)) BEGIN
EXEC sp_addextendedproperty 'MS_Description',
@value = N'資料表',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'[TABLE]'
END ELSE BEGIN
EXEC sp_updateextendedproperty 'MS_Description',
@value = N'資料表',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'[TABLE]'
END
--查詢單一Table解釋
Select * From ::fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', '[TABLE]', NULL, NULL)
--查詢所有Table解釋
Select * From ::fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', NULL, NULL, NULL)

 

--建立Column
Alter Table [TABLE] Add pkName VARCHAR(255) Constraint DF_TABLE_pkName Default '' Constraint PK_TABLE_pkName Primary Key(pkName) NOT NULL
--修改Column
Alter Table [TABLE] Alter Column pkName VARCHAR(MAX) NOT NULL
Alter Table [TABLE] Add Constraint PK_TABLE_pkName Primary Key(pkName)
Alter Table [TABLE] Add Constraint DF_TABLE_pkName Default ''
--刪除Column
Alter Table [TABLE] Drop Column pkName
--搜尋Column
Select * From INFORMATION_SCHEMA.COLUMNS
--重新命名Column
EXEC sp_rename 'tableName.pkName','pkName_New','COLUMN'
--對Column加上解釋
IF Not Exists(Select TOP 1 1 From ::fn_listextendedproperty(NULL,'SCHEMA','dbo','TABLE','[TABLE]','COLUMN','pkName') BEGIN
EXEC sp_addextendedproperty @name = N'MS_Description',
  @value = N'欄位',
  @level0type = N'SCHEMA',
  @level0name = N'dbo',
  @level1type = N'TABLE',
  @level1name = N'[TABLE]',
  @level2type = N'COLUMN',
  @level2name = N'pkName'
END ELSE BEGIN
EXEC sp_addextendedproperty @name = N'MS_Description',
  @value = N'欄位',
  @level0type = N'SCHEMA',
  @level0name = N'dbo',
  @level1type = N'TABLE',
  @level1name = N'[TABLE]',
  @level2type = N'COLUMN',
  @level2name = N'pkName'
END
--查詢單一Column解釋
Select * From ::fn_listextendedproperty(NULL, 'SCHEMA','dbo','TABLE','[TABLE]','COLUMN','pkName')
--查詢所有Column解釋
Select * From ::fn_listextendedproperty(NULL, 'SCHEMA','dbo','TABLE','[TABLE]','COLUMN',NULL)

※SQL Server 2016開始支援修改欄位順序，2016之前的版本只能土法煉鋼(將Table做備份→刪除原Table→建立新的順序Table→將備份Table資料倒至新的Table→刪除備份Table)，SQL Server 2016還支援很多新的Function和效能，有興趣的朋友可以去官網看看。

 

--建立Data
Insert into TABLE(pkName) Values('pkData')
--修改Data
Update [TABLE] Set pkName = 'pkData' Where pkName = 'pkData'
--刪除Data
Delete From [TABLE] Where pkName ='pkData'
--搜尋Data
Select * From [TABLE]

 

--設定關聯其他資料庫
EXEC sp_addlinkedserver @server = 'SERVER-NAME'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER-NAME' , @useself = false, @rmtuser = 'userID', @rmtpassword = 'pwd'
Select TOP 10 * From [SERVER-NAME].[DATABASE].[dbo].[TABLE]
--查詢已關聯的資料庫
select * from sys.servers
--移除已關聯的資料庫
EXEC sp_dropserver @server = 'SERVER-NAME'

 

--新增使用者
CREATE LOGIN [userID] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[DATABASE], DEFAULT_LANGUAGE=[Traditional Chinese], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
--帳號啟用
ALTER LOGIN [UserID] ENABLE
--設定權限
CREATE USER [UserID] FOR LOGIN [UserID];
EXEC sp_addrolemember N'db_owner', N'UserID'

 

如有觀念錯誤，請不吝指教