SQL Server之DDL语句总结

--删除表
DROP TABLE TABLE_01
GO

--创建表
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 0
GO
ALTER 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

--查询索引，是否唯一、是否主键
SELECT
I.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