从SQL Server表扩展属性 [英] Select Extended Property from SQL Server tables

问题描述

我写一个简单的CMS为客户的数据库。共有12桌，他们需要管理的4人全部数据。

I am writing a simple CMS for a client's database. There are 12 tables and they need to manage all of the data in 4 of them.

我设置了​​一个动态数据项目（LINQ到SQL因为这是我最熟悉的），并在第一页使得所有可用的12表的编辑，因为它应该。这些都是在一个名为visibleTables枚举列表。

I set up a dynamic data project (Linq-to-SQL as that's what I'm most familiar with) and the first page makes all 12 tables available for edit as it should. These are in an enumerable list called visibleTables.

接下来，我走进SSMS并创建了一个名为扩展性USEREDIT并将其设置为0或1，具体取决于用户是否应该看到它的屏幕上。

Next I went into SSMS and created an extended property called UserEdit and set it to 0 or 1 depending on whether the user should see it on that screen.

我很想做的是过滤visibleTables名单由扩展属性，但不知道怎么样。

What I would love to do is filter the visibleTables list by that extended property but don't know how to.

我能看到表的列表，并与此查询的属性：

I am able to see the list of tables and the property with this query:

select major_id, name, value
from sys.extended_properties
where name = 'UserEdit'

和我是通过visibleTables将循环并删除那些与0，但是我还没有想通了这一点无论是尚未为major_id似乎并没有成为一个属性，我可以找到。

and I was going to loop through the visibleTables and remove the ones with 0s but I haven't figured that out either yet as the major_id doesn't seem to be a property I can find.

推荐答案

那么，根据<一href="http://msdn.microsoft.com/en-us/library/ms177541.aspx">http://msdn.microsoft.com/en-us/library/ms177541.aspx, major_id为你的类扩展属性（OBJECT_OR_COLUMN）真表示OBJECT_ID。所以下面的查询会得到大家的扩展属性以及它们所属的表：

Well, according to http://msdn.microsoft.com/en-us/library/ms177541.aspx, "major_id" for your class of extended properties (OBJECT_OR_COLUMN) really indicates the "object_id". So the following query would get you all the extended properties along with the tables they belong to:

select p.*, t.*
from sys.extended_properties p
inner join sys.tables t on p.major_id = t.object_id
where class = 1

您可以过滤它，只要你喜欢，但我知道，如果你需要帮助。

You can filter it as you like, but let me know if you need help.

这篇关于从SQL Server表扩展属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！