SqlServer2012 扩展属性查询不到

Question

抱歉，发帖时还在加班，说的比较仓促。我这边的情况是几个组联合开发一个平台，有多个子系统，我们负责数据仓库部分。其中一个组的系统表结构变动较大，项目又需要PDM文档保持最新。一个个表改又麻烦，就去试了下直接从他们数据库导。但是导进来表和列的name不是中文的，我又网上搜了下，可以改动SqlServer DBMS的script来实现导入扩展属性。问题是还是不成功。建表时的语句：/****** Object:  Table [dbo].[TBL_ROLEPOWERSET]    Script Date: 07/29/2014 12:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_ROLEPOWERSET](
[RPID] [int] IDENTITY(1,1) NOT NULL,
[ROLEID] [varchar](20) NULL,
[MENUID] [varchar](30) NULL,
[ISADD] [char](1) NULL,
[ISUPD] [char](1) NULL,
[ISDEL] [char](1) NULL,
 CONSTRAINT [PK_TBL_ROLEPOWERSET] PRIMARY KEY CLUSTERED 
(
[RPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'RPID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'ROLEID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'菜单id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'MENUID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否新增(N  否   Y  是)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'ISADD'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否修改(N  否   Y  是)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'ISUPD'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否删除(N  否   Y  是)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET', @level2type=N'COLUMN',@level2name=N'ISDEL'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色权限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TBL_ROLEPOWERSET'
GO
我的想法获取这个扩展属性MS_Description传给PDM中的COLNNAME。下面是PDM中DBMS的script。MSSQLSERVER2008::Script\Objects\Column\SqlListQuery{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COLNNAME, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}select
    u.name,
    o.name,
    c.column_id,
    c.name,
    case when c.system_type_id in (165, 167, 231) and c.max_length = -1 then t.name + '(Max)' else t.name end,
    c.precision,
    case (c.max_length) when -1 then 0 else case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else (c.max_length) end end as colnA,
    c.scale,
    case(c.is_computed) when 1 then convert(varchar(8000), (select z.definition from [%CATALOG%.]sys.computed_columns z where z.object_id = c.object_id and z.column_id = c.column_id)) else '' end as colnB,
    case(c.is_nullable) when 1 then 'NULL' else 'NOTNULL' end,
    case(c.is_identity) when 1 then 'identity' else '' end,
    case when(c.user_type_id <> c.system_type_id) then (select d.name from [%CATALOG%.]sys.types d where d.user_type_id = c.user_type_id) else '' end as colnC,
    convert(varchar(8000), d.definition),
    case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' + convert(varchar, i.increment_value) else '' end as colnD,
    (select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') as colnE,
    c.collation_name,
    case (i.is_not_for_replication) when 1 then 'true' else 'false' end,
    d.name,
    case(c.is_sparse) when 1 then 'true' else 'false' end,
    case(c.is_filestream) when 1 then 'true' else 'false' end,
    case(c.is_rowguidcol) when 1 then 'true' else 'false' end
from
    [%CATALOG%.]sys.columns      c
    join [%CATALOG%.]sys.objects o on (o.object_id = c.object_id)
    join [%CATALOG%.]sys.schemas u on (u.schema_id = o.schema_id)
    join [%CATALOG%.]sys.types   t on (t.user_type_id = c.system_type_id)
    left outer join [%CATALOG%.]sys.identity_columns i on (i.object_id = c.object_id and i.column_id = c.column_id)
    left outer join [%CATALOG%.]sys.default_constraints d on (d.object_id = c.default_object_id)
where 
   o.type in ('U', 'S', 'V')
[  and u.name = %.q:OWNER%]
[  and o.name=%.q:TABLE%]
order by 1, 2, 3其中"(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') as colnE"应该是获取扩展属性的语句。开始我以为是参数问题，第二个参数'user'应该改成'SCHEMA'。但是去query了一下，还是不行。后来我干脆用在线帮助提供的列出全部扩展属性的语句（帖子里列出来的）跑了一遍，没有结果。但是我右键列名，属性里的扩展属性标签里又是有MS_Description和对应的值的，好奇怪。

Answer 1

对应你右键操作的字段，先返回全部的 fn_listextendedproperty() 结果，看看有哪些属性。
会不会属性名区分大小写？

Answer 2

SELECT *
FROM sys.extended_properties
用你的建表语句，然后用这个可以查到

Answer 3

问题找到了，我用来query的那个字段确实没扩展属性。大意了，正好挑到那个...右键看的是另一个。
现在已经成功导入了，唯一困惑的是为什么查询全部扩展属性的query只返回了数据库本身的扩展属性，而不是包括表和列的扩展属性。
SELECT objtype, objname, name, value
FROM ::fn_listextendedproperty(default, default, default, default, default, default, default);

Answer 4

是的，我上午也在网上看到了，就是查的这个才发现我查询的那个字段确实没有添加扩展属性。我看的那篇文章是自己建数据字典表的query。
发现对数据库还是相当的不了解，还得需要多多学习。

Answer 5

问题找到了，我自己右键查看的和query的不是一个字段，汗……

Answer 6

联机丛书说明：sys.fn_listextendedproperty (Transact-SQL) 
返回数据库对象的扩展属性值。