Allscripts Project

Last week I worked on one of my projects for Allscripts Vision accounting to add a few enhancements.

The major enhancement was the ability to export a detailed report for all users to Excel and create a Pivot table with chart of users’ logins over the report’s timeline.

You can read more on my project page for this project.

Allscripts ProEHR 9.10.3 database table and column extended properties text:  ProEHR 9.10.3 – Table and Column Extended Properties

14 thoughts on “Allscripts Project

  1. Charles Dasher

    Been looking everywhere but cannot seem to find this. Perhaps you can help. What table is a Patients History stored in? Specifically, I am looking for Patients who are smokers. I have managed to find the following tables: HPSITE.HISTORYCATEGORY and HPSITE.HISTORYKNOWLEDGE, but cannot for the life of me figure out how these map to a patient. Any help would be appreciated.

  2. Rich Kreider Post author

    Hi Charles,

    You want HPSITE.HXDIAGNOSIS table which provides CATEGORY_ID column and a value of 25 (Allergy = 21, Immunization = 22, Family = 23, PastMedical = 24, Social = 25, Travel = 26, Pregnancy = 28)

    In HPSITE.HXDIAGNOSIS you will find PATIENT_ID column to reference against HPSITE.DEMOGRAPHICS table on column DEM_EXTERNALID.

    Hope this helps.

    Edit: I re-read what was being asked and this didn’t really answer that, sorry. I will take a look at the tables I have again. In the meantime, I have an Excel sheet I made that contains all the Table and Column extended properties (descriptions of what tables/columns are for). Luckily Allscripts documented this. =) I will edit this post and the link will be at the bottom.

  3. Charles Dasher

    It seems that if I get PATIENT_NUMBER from PM.PATIENTS




    I think I have it. Could NOT have done it without your spreadsheet. I am very grateful for your help. Thank you so much Rich.


    1. Rich Kreider Post author

      Hi Charles,

      I did a little more work on trying to get a combined, easily searched, file for the database info and came up with the following after a little tinkering:

      (That is for ProEHR)

      I also built a compiled html file (Windows Help File) that is portable and ~6MB in size if interested. I have needed to do this for some time now since I’m constantly trying to ‘reverse engineer’ to solve problems for our clients.

  4. Rich Kreider Post author

    I don’t have Allscripts PM unfortunately – I can give you the TSQL to run a query and you can save it to a CSV and import to Excel and tidy up if you want.

    SELECT + '.' + AS [table],
                td.value AS [table_desc],
           AS [column],
                    cd.value AS [column_desc]
    FROM            sysobjects t
    INNER JOIN  sysusers u
        ON          u.uid = t.uid
    LEFT OUTER JOIN sys.extended_properties td
        ON          td.major_id =
        AND         td.minor_id = 0
        AND = 'MS_Description'
    INNER JOIN  syscolumns c
        ON =
    LEFT OUTER JOIN sys.extended_properties cd
        ON          cd.major_id =
        AND         cd.minor_id = c.colid
        AND = 'MS_Description'
    WHERE t.type = 'u'
    ORDER BY [table], [column] ASC
    1. Charles Dasher

      It seems that does the trick for dbo tables but not for the rest, is there something that I could be doing wrong when I run it? I am just selecting the database and then new Query and executing it.

  5. Charles Dasher

    Once again I cannot even begin to thank you for the help. I am stuck on two remaining issues for my task. I am able to get the data out for smoking (thanks to your advice and help), however if I try and insert a new record (I am a newbie at Allscripts and MSSQL ) indicating that a patient smokes. The first problem is that It appears that the primary key for the HX_DIAGNOSIS table is pulled from some HXDIAGNOSIS_SEQ table (which seems to use the sequence table and some magic) and I cannot for the life of me figure out how to insert the next value (I can of course get the max value and increment it, but this causes the desktop client app to complain about a duplicate primary key when any other item is added via the GUI) obviously they know how to get the value into the table and I do not. If I do insert a new record (and break the client just for fun 🙂 into the HX_DIAGNOSIS table, it does not show up in the client application. In the meantime, I will run your query which will be of great use as a database diagram in SQLServer is too large to view at a size that is both readable and comprehensible 🙂

    1. Rich Kreider Post author

      I will take a look at this when I’m back at the office Monday to see how it’s being entered. I believe it is a stored procedure (most of Allscripts’ routines involve their stored procedures).


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.