SQLSERVER-字段说明

添加字段的名称

EXEC  
sys.sp_addextendedproperty @name=N'MS_Description',  
@value=N'字段说明' , @level0type=N'SCHEMA',@level0name=N'dbo',  
@level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',  
@level2name=N'字段名' 
GO 

 

修改字段的名称

BEGIN TRANSACTION  
GO  
DECLARE @v sql_variant  
SET @v = N'说明信息' 
EXECUTE sys.sp_updateextendedproperty N'MS_Description',  
@v, N'SCHEMA',N'dbo',N'TABLE',N'表名, N'COLUMN', N'字段名'  
GO  
COMMIT 

 

查询字段的信息

WITH TableField AS (
                       SELECT TableName = OBJECT_NAME(c.object_id),
                              FieldName = c.name,
                              FieldDescription = ex.value,
                              FieldType = UPPER(
                                  CASE 
                                       WHEN t.name = 'int' THEN t.name
                                       WHEN t.name = 'datetime' THEN t.name
                                       ELSE t.name + '(' + CONVERT(VARCHAR(10), c.max_length) 
                                            +
                                            ')'
                                  END
                              ),
                              KeyField = CASE 
                                              WHEN kc.CONSTRAINT_NAME IS NULL THEN 
                                                   'R'
                                              ELSE 'K'
                                         END
                       FROM   sys.columns c
                              LEFT OUTER JOIN sys.extended_properties ex
                                   ON  ex.major_id = c.object_id
                                   AND ex.minor_id = c.column_id
                                   AND ex.name = 'MS_Description'
                              LEFT OUTER JOIN sys.types t
                                   ON  c.user_type_id = t.user_type_id
                              LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                                   kc
                                   ON  OBJECT_NAME(c.object_id) = kc.TABLE_NAME
                                   AND c.name = kc.COLUMN_NAME
                       WHERE  OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0
                              AND OBJECT_NAME(c.object_id) = 'Table'
                   )

SELECT *
FROM   TableField