List CRM entity attributes and data types

Hi,

I am using CRM 3.0 and have a requirement to list all the the tables, attributes names and display names and datatypes. Is there any easy way to export this information from the CRM tool or prepare a SQL query that will list the information?

You are kind of in the wrong neck of the woods, but I will try to give you a bit of help (there is a page here to go to the Microsoft Dynamics newsgroups http://www.microsoft.com/dynamics/community/newsgrouplanding.mspx)

Most of this information is available in the METABASE database in the entity and attribute tables. Not 100% sure if the display names are exactly right. They might actually be maintained in an ugly XML document.

|||Use CRM Meta Webservices. You can use the CRM SDK to get info on methods that would do this.

List Attribute Names for Single Node

Hello,

I am trying to accomplish the following goal:

"For any given node in an xml variable, return via recordset a list of attributes."

Take the following query:

DECLARE @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'

SELECT x.value('@.Key','varchar(255)') as [Key],
x.value('@.Value','varchar(255)') as [Value]
FROM @.x.nodes('//Item/Data') Data(x)

That will return:

Key | Value
-
ID | 1001
Name | Blue
Type | Color

What I want is to be able to query @.x ahead of time such that I get back something to the effect of:

Attribute

Key
Value

The reason is that I want to be able to build the columns of my SELECT statement dynamically by iterating through the attributes of a node. I would then execute my prepared statement to get that Key/Value recordset back.

Ignoring the structure of my example XML, what I'm returning in my Key/Value recordset, and how I'm going about returning it...all I want is to know whether or not I can get via recordset a list of attributes for an XML node, and if I can, how I do it.

Thanks!
Daniel

This is a bit clumsey, but should work.

WITH AllAttr(Vals)
AS
(SELECT @.x.query('<root>{for $a in /Item/Data/@.* return <attr>{$a}</attr>}</root>'))
SELECT DISTINCT
x.value('local-name(@.*[1])','varchar(255)') as [Attribute]
FROM AllAttr
CROSS APPLY Vals.nodes('/root/attr') Data(x)

|||

For any given node, for example <Data>, you do:

declare @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'
select distinct x.value('local-name(.)', 'varchar(20)')
from @.x.nodes('//Data/@.*') as ref(x)