SQL Server数据库DDL语句总结
沉沙 2018-05-09 来源 : 阅读 1041 评论 0

摘要:本文对SQL Server数据库的DDL语句使用进行了详细总结,希望读过本文以后可以帮您厘清SQL Server数据库知识点。

--删除表DROP TABLE TABLE_01GO
--创建表CREATE TABLE TABLE_01 (
  [TEST_FIELD_Z][varchar] (5) NOT NULL DEFAULT ' ' ,
  [TEST_FIELD_B][datetime]  NOT NULL DEFAULT (getdate()) ,
  [TABLE_ITEM_SEQ][numeric] (5) NOT NULL DEFAULT 0 ,
  [SEQ_DESC]    [varchar] (40) NOT NULL DEFAULT ' ' ,
  [TEST_FIELD_D][datetime]  NOT NULL DEFAULT (getdate())
)GO
--添加列中文描述
exec sp_addextendedproperty N'MS_Description', N'测试Z', N'user', N'dbo', N'table', N'TABLE_01', N'column', N'TEST_FIELD_Z'      GO
exec sp_addextendedproperty N'MS_Description', N'测试B', N'user', N'dbo', N'table', N'TABLE_01', N'column', N'TEST_FIELD_B'      GO
exec sp_addextendedproperty N'MS_Description', N'序号', N'user', N'dbo', N'table', N'TABLE_01', N'column', N'TABLE_ITEM_SEQ'      GO
exec sp_addextendedproperty N'MS_Description', N'序号描述', N'user', N'dbo', N'table', N'TABLE_01', N'column', N'SEQ_DESC'      GO
exec sp_addextendedproperty N'MS_Description', N'测试D', N'user', N'dbo', N'table', N'TABLE_01', N'column', N'TEST_FIELD_D'      GO
--删除列描述
EXEC [sys].[sp_dropextendedproperty] @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'TABLE_01', @level2type = N'COLUMN', @level2name = N'TEST_FIELD_NUM';
GO
--更新列描述
EXEC [sys].[sp_updateextendedproperty] @name = N'MS_Description', @value = N'测试2', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'TABLE_01', @level2type = N'COLUMN', @level2name = N'TEST_FIELD_NUM';
GO

--加、删列。删列时需要先删除列上的约束和索引,参见后面的说明和语句。

ALTER TABLE [dbo].[TABLE_01] ADD [TEST_FIELD_NUM] numeric(3, 2) NOT NULL DEFAULT 0GOALTER TABLE [dbo].[TABLE_01] DROP COLUMN [TEST_FIELD_NUM]GO

--改列,如果存在约束、索引,需要先删除再创建。字段的默认值也是一种约束,系统会自动生成一个约束名,后附查询语句。

ALTER TABLE [dbo].[TABLE_01]DROP CONSTRAINT [DF__TTATESTTA__TABLE__2AF556D4]
DROP INDEX [TESTTABLE_INEW] ON [dbo].[TABLE_01]DROP INDEX [TABLE_01_IDX] ON [dbo].[TABLE_01]
ALTER TABLE [dbo].[TABLE_01]ALTER COLUMN [TABLE_ITEM_SEQ] numeric(5, 3) NOT NULL
ALTER TABLE [dbo].[TABLE_01]
 ADD DEFAULT ((0)) FOR [TABLE_ITEM_SEQ]
CREATE INDEX [TESTTABLE_INEW]
 ON [dbo].[TABLE_01] ([TABLE_ITEM_SEQ])WITH (FILLFACTOR=100,
DATA_COMPRESSION = NONE)ON [PRIMARY]
CREATE UNIQUE INDEX [TABLE_01_IDX]
 ON [dbo].[TABLE_01] ([TABLE_ITEM_SEQ], [SEQ_DESC])WITH (FILLFACTOR=100,
DATA_COMPRESSION = NONE)ON [PRIMARY]

--创建索引。聚集索引与非聚集索引的区别是物理数据的存放是否依据索引顺序存放,一个表只能有一个聚集索引,默认是非聚集索引。--主键是一个UNIQUE CLUSTERED索引,但是创建一个唯一聚集索引却不是主键,

只能显式创建CONSTRAINT [name] PRIMARY KEY,参见之后的语句。

CREATE UNIQUE NONCLUSTERED INDEX [TABLE_01_IDX]ON [TABLE_01]
([TABLE_ITEM_SEQ] , [SEQ_DESC])WITH
(
PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = NONE
)ON [PRIMARY];
--删除索引DROP INDEX [TABLE_01_I] ON [BSM2].[dbo].[TABLE_01];
GO

--主键添加,删除。

ALTER TABLE TABLE_01 ADD CONSTRAINT TABLE_01_PK PRIMARY KEY (FIELD1,FIELD2)
GO
ALTER TABLE TABLE_01 DROP CONSTRAINT TABLE_01_PK
GO

--改名

EXEC sp_rename  'OLD_TABLE' ,'NEW_TABLE';

--查询表字段(类型、长度、是否允许为空、是否自增、是否被索引、是否有约束、约束名)

--特殊说明:

--NVARCHAR查询出的长度是创建的两倍,因为使用UNICODE,其他如NCHAR, NTEXT--NUMERIC类型字段的长度由整数部分长度和小数部分长度组成,例如123.45,其precision是5,scale是2,size是5-2=3

--TIMESTAMP不是时间类型,和DB2不一样
SELECT DISTINCT 
(CASE WHEN ISNULL(CTT.is_track_columns_updated_on,0) <> 0 THEN is_track_columns_updated_on ELSE 0 END) AS HasChangeTrackingTrackColumn,
(CASE WHEN ISNULL(CTT.object_id,0) <> 0 THEN 1 ELSE 0 END) AS HasChangeTracking,
TTT.lock_escalation_desc,
T. type AS ObjectType,
C.Name,
C.is_filestream,
C.is_sparse,
S4.Name as OwnerType,
C. user_type_id,
C.Column_Id AS ID,
C.max_length AS Size,
C.Precision,
C.Scale,
ISNULL(C.Collation_Name,'') as Collation,
C.Is_nullable AS IsNullable,
C.Is_RowGuidcol AS IsRowGuid,
C.Is_Computed AS IsComputed,
C.Is_Identity AS IsIdentity,
COLUMNPROPERTY(T.object_id,C.name,'IsIdNotForRepl') AS IsIdentityRepl,
IDENT_SEED('[' + S1.name + '].[' + T.Name + ']') AS IdentSeed,
IDENT_INCR('[' + S1.name + '].[' + T.Name + ']') AS IdentIncrement,
ISNULL(CC.Definition,'') AS Formula, ISNULL(CC.Is_Persisted,0) AS FormulaPersisted,
CASE WHEN ISNULL(DEP.column_id,0) = 0 THEN 0 ELSE 1 END AS HasComputedFormula,
CASE WHEN ISNULL(IC.column_id,0) = 0 THEN 0 ELSE 1 END AS HasIndex,
TY.Name AS Type, '[' + S3.Name + '].' + XSC.Name AS XMLSchema,
C.Is_xml_document, TY.is_user_defined, ISNULL(TT.Name,T.Name) AS TableName,
T.object_id AS TableId,S1.name AS TableOwner,Text_In_Row_limit,
large_value_types_out_of_row,
ISNULL(objectproperty(T.object_id, N'TableHasVarDecimalStorageFormat'),0) AS HasVarDecimal,
OBJECTPROPERTY(T.OBJECT_ID,'TableHasClustIndex') AS HasClusteredIndex,
DSIDX.Name AS FileGroup,ISNULL(lob.Name,'') AS FileGroupText,
ISNULL(filestr.Name,'') AS FileGroupStream,
ISNULL(DC.object_id,0) AS DefaultId,
DC.name AS DefaultName,
DC.definition AS DefaultDefinition,
C.rule_object_id,
C.default_object_id
FROM sys.columns C 
INNER JOIN sys.objects T ON T.object_id = C.object_id
INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id             
LEFT JOIN sys.indexes IDX ON IDX.object_id = T.object_id and IDX.index_id < 2 LEFT JOIN sys.data_spaces AS DSIDX ON DSIDX.data_space_id = IDX.data_space_id LEFT JOIN sys.table_types TT ON TT.type_table_object_id = C.object_id
LEFT JOIN sys.tables TTT ON TTT.object_id = C.object_id
LEFT JOIN sys.schemas S1 ON (S1.schema_id = TTT.schema_id and T.type = 'U') OR (S1.schema_id = TT.schema_id and T.type = 'TT')
LEFT JOIN sys.xml_schema_collections XSC ON XSC.xml_collection_id = C.xml_collection_id LEFT JOIN sys.schemas S3 ON S3.schema_id = XSC.schema_id
LEFT JOIN sys.schemas S4 ON S4.schema_id = TY.schema_id
LEFT JOIN sys.computed_columns CC ON CC.column_id = C.column_Id AND C.object_id = CC.object_id
LEFT JOIN sys.sql_dependencies DEP ON DEP.referenced_major_id = C.object_id AND DEP.referenced_minor_id = C.column_Id AND DEP.object_id = C.object_id
LEFT JOIN sys.index_columns IC ON IC.object_id = T.object_id AND IC.column_Id = C.column_Id
LEFT JOIN sys.data_spaces AS lob ON lob.data_space_id = TTT.lob_data_space_id LEFT JOIN sys.data_spaces AS filestr ON filestr.data_space_id = TTT.filestream_data_space_id
LEFT JOIN sys.default_constraints DC ON DC.parent_object_id = T.object_id AND parent_column_id = C.Column_Id
LEFT JOIN sys.change_tracking_tables CTT ON CTT.object_id = T.object_id
WHERE T.type IN ('U','TT')
--AND T.name LIKE 'TABLE_01'
ORDER BY ISNULL(TT.Name,T.Name),T.object_id,C.column_id
--查询索引,是否唯一、是否主键SELECTI.Name,
OBJ.name AS TABLE_NAME,C.column_id,C.Name AS COLUMN_NAME,
IC.key_ordinal AS COLUMN_POSITION, I.index_id,
is_unique,
is_primary_key
FROM sys.indexes I 
INNER JOIN SYS.all_objects OBJ ON OBJ.object_id = I.object_id AND OBJ.type = 'U'
INNER JOIN sys.index_columns IC ON IC.index_id = I.index_id AND IC.object_id = I.object_id
INNER JOIN sys.columns C ON C.column_id = IC.column_id AND IC.object_id = C.object_id
WHERE I.type IN (1,2,3)
--AND OBJ.name LIKE 'TABLE_01'
AND objectproperty(I.object_id, 'IsMSShipped') <> 1 
ORDER BY OBJ.NAME, I.Name, IC.key_ordinal

以上内容由职坐标整理发布,学习更多的SQL Server知识,请关注职坐标SQL Server频道!

本文由 @沉沙 发布于职坐标。未经许可,禁止转载。
喜欢 | 0 不喜欢 | 0
看完这篇文章有何感觉?已经有0人表态,0%的人喜欢 快给朋友分享吧~
评论(0)
后参与评论

您输入的评论内容中包含违禁敏感词

我知道了

助您圆梦职场 匹配合适岗位
验证码手机号,获得海同独家IT培训资料
选择就业方向:
人工智能物联网
大数据开发/分析
人工智能Python
Java全栈开发
WEB前端+H5

请输入正确的手机号码

请输入正确的验证码

获取验证码

您今天的短信下发次数太多了,明天再试试吧!

提交

我们会在第一时间安排职业规划师联系您!

您也可以联系我们的职业规划师咨询:

小职老师的微信号:z_zhizuobiao
小职老师的微信号:z_zhizuobiao

版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
 沪公网安备 31011502005948号    

©2015 www.zhizuobiao.com All Rights Reserved

208小时内训课程