SQL数据库表字段明细导入导出

添加表字段备注信息

EXECUTE sp_addextendedproperty N'MS_Description', '字段描述信息', N'user', N'dbo', N'table', N'表名称', N'column', N'字段名称'

删除表字段备注信息

EXEC sp_dropextendedproperty 'MS_Description', 'user', dbo, 'table', N'表名称', 'column', N'字段名称'

查询表字段明细，其中主键判断简单用自增ID来判断的，如果需要精确判断，可用sysobjects中Xtype为PK的列查看

SELECT     A.name AS table_name,                      B.name AS column_name,  T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(B.scale AS VARCHAR) +')'     -- time  
            WHEN 42 THEN '('+CAST(B.scale AS VARCHAR) +')'                                      -- datetime2  
            WHEN 43 THEN '('+CAST(B.scale AS VARCHAR) +')'                                      -- datetimeoffset  
            WHEN 106 THEN '('+CAST(B.precision  AS VARCHAR)+','+ CAST(B.scale AS VARCHAR) +')'  -- decimal  
            WHEN 108 THEN '('+CAST(B.precision  AS VARCHAR)+','+ CAST(B.scale AS VARCHAR) +')'  -- numeric  
            WHEN 165 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1) AS VARCHAR),'MAX') +')'       -- varbinary  
            WHEN 167 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1) AS VARCHAR),'MAX') +')'       -- varchar  
            WHEN 173 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1) AS VARCHAR),'MAX') +')'       -- binary  
            WHEN 175 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1) AS VARCHAR),'MAX') +')'       -- char  
            WHEN 231 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1)/2 AS VARCHAR),'MAX') +')'     -- nvarchar(该字段校检根据实际情况)  
            WHEN 239 THEN '('+ISNULL(CAST(NULLIF(B.max_length,-1) AS VARCHAR),'MAX') +')'       -- nchar  
            ELSE ''  
            END AS '数据类型'  ,
    case when B.is_nullable =1 then '是' else '否' end as '可空',
    case when B.is_identity =1 then '是' else '否' end as '主键'   ,      C.value AS '备注'
FROM sys.tables A
INNER JOIN sys.columns B     ON B.object_id = A.object_id
INNER JOIN sys.types T 
    ON T.user_type_id = B.user_type_id
LEFT JOIN sys.extended_properties C     ON C.major_id = B.object_id         AND C.minor_id = B.column_id
--WHERE A.name = '表名'