sql-server常用ddl-sql

数据库的操作

查看当前所有库

select name,database_id,create_date from sys.databases
go

创建库

USE master;  
GO  
CREATE DATABASE Sales  
ON   
( NAME = Sales_dat,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = Sales_log,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH = 5MB );  
GO

使用库

USE master ; 
GO

删除库

USE master ;  
GO  
DROP DATABASE Sales, NewSales ;  
GO

修改库

来源于官网
修改属性

USE AdventureWorks2012;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO

修改配置

USE master;
GO
ALTER DATABASE AdventureWorks2012 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

增加库大小

USE master;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

收缩数据库

DBCC SHRINKDATABASE (UserDB, 10);
GO

表的操作

查询当前库的表

select name from sys.tables;
go

直接查询系统目录视图，查询有关表、架构和列的对象元数据信息

SELECT s.name as schema_name, t.name as table_name, c.* FROM sys.columns AS c
INNER JOIN sys.tables AS t ON t.object_id = c.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name = 'mytable' AND s.name = 'dbo';

查看列信息

EXEC sp_help 'dbo.mytable';

备注，没有提供建表语句

重命名

USE AdventureWorks2012;
GO
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

添加列

ALTER TABLE dbo.doc_exa 
ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

删除列

ALTER TABLE dbo.doc_exb DROP COLUMN column_b;
GO

重命名列

EXEC sp_rename 'dbo.ErrorLog.ErrorTime', 'ErrorDateTime', 'COLUMN';

修改列

CREATE TABLE dbo.doc_exy (column_a INT );  
GO  
INSERT INTO dbo.doc_exy (column_a) VALUES (10);  
GO  
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);  
GO

添加约束

建表添加唯一约束

USE AdventureWorks2012;  
GO  
CREATE TABLE Production.TransactionHistoryArchive4  
 (  
   TransactionID int NOT NULL,   
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)   
);   
GO

已有的表添加唯一约束

USE AdventureWorks2012;   
GO  
ALTER TABLE Person.Password   
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);   
GO

删除约束

-- Return the name of unique constraint.  
SELECT name  
FROM sys.objects  
WHERE type = 'UQ' AND OBJECT_NAME(parent_object_id) = N' DocExc';  
GO  
-- Delete the unique constraint.  
ALTER TABLE dbo.DocExc   
DROP CONSTRAINT UNQ_ColumnB_DocExc;  
GO

相关引用

https://learn.microsoft.com/zh-cn/sql/relational-databases/tables/delete-unique-constraints?view=sql-server-2017