Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Get Table Id and Field...
Finance forum
Answered

Get Table Id and Field Ids of D365 F&O Table through SQL query

Posted on by 742

Hi Everyone,

Using below SQL query I can get the Table Id,

select * from SysTableIdView where Name = 'CustTable'

Can anyone please help me with SQL query to get field Ids of all fields of a particular table? This is possible and I have done this long time back, so unable to recall it.

I don't want to fetch field Id of a single field. I want the list of all field names and their IDs.

Thank you in advance.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Table and field IDs are technical IDs that are intended for D365 internal usage. As long as you're working inside D365, you can use the mentioned views in x++ to get the info. And if you're working outside D365, then table and field ids should be irrelevant.

    Please let us know what is your functional requirement. Thanks!

  • Martin Dráb Profile Picture
    Martin Dráb 225,588 Super User on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Why would you do that?

    Please tell us what you're trying to achieve from business point of view. We can't suggest a meaningful technical design without knowing what you need to achieve.

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Hi Andre,

    Then how could we identify the IDs for UAT and Production environment? As we don't have SQL access of UAT and Production in D365, do I need to write X++ job for same?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,986 Super User on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Hi Rhushikesh,

    A small additional comment. Note that the IDs can be different in each environment.

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Thank you Nikolaos for quick response.

    I am able to fetch field Ids with below query now.

    select * from SysTableFieldIdView where SysTableFieldIdView.Id = '77'

    In above Query,  SysTableFieldIdView.Id (77) is a Table Id

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: Get Table Id and Field Ids of D365 F&O Table through SQL query

    Try SysTableFieldIdView.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans