Just try to use the SQL query to read out the drawings in "Document Attributes" Environment for Attribute value (PW_DOC_DESCRIPTION).
I can read out the pdf files in "Rendition Attributes" environment for Attribute value (PW_DOC_DESCRIPTION) from database table named "env_rendattr". What is the attribute database table name for Document Attributes please?
Thanks for your help.
Ritchie, Each time you create an Environment, it creates a new table to store those Attributes. However, the general document properties such as Name, Description and Filename are held in the DMS_DOC table. The field names are O_ITEMNAME, O_ITEMDESC, and O_FILENAME. Is that what you are looking for?
Really appreciated your reply. Yes I agree with you about the each Environment will create a new table (I guess the same as what you said). Also you are correct the O_ITMEDESC is the description in the common tab in ProjectWise. But I struggle to read out the description inside of the environment using SQL query. eg. environment name is "Rendition Attributes" then database table name is "env_rendattr". But what or how can I find out the database table name if environment name is "Document Attributes". If I know the database table name, I can do select * from <tablename> to find out all columns in the table then to try out the one for attributes.
Also which table has the attribute for "foldername" please?
If there is a ER diagram for ProjectWise database?
Take a look at the dms_tab table. It will give the table names for each of the environments.
Something like this should work.
SELECT * FROM dms_tabs WHERE o_tabno = (SELECT o_tabno FROM dms_env WHERE o_envname LIKE 'Simple')
What I've found so far:
dms_tabs (Explaining first as its used below)A list of your environment tablenames.
dms_projAll Project(Work Areas) and standard Folders are listed as a project.Projects/Work Areas have an [o_type] of 2 where standard folders use 0.[o_envno] is used for the Environment, however it is prefixed by an additional 1 (Unsure why and unsure if after your Environments hit 100 if this will still occur). Example, [o_envno] of 114 = 14 in dms_tabs. I can use this to find which table I need to call from. (Remove the first character)
dms_docAll file/document information. Name, Desc, Version, Workflow, State, etc (not counting additional environment info)I use the [o_projectno] and [o_itemno] when I want to look up information against my Environment table. ($VAULT$ and $DOCUMENTNO$ fields match these in PWAdministrator)
envXXXXX (Custom Environments)All your custom environment attributes with some basic information copied via dms_doc ([a_version],[o_projectno],[o_itemno],[a_creatorno],[a_createtime],[a_updatorno])
dms_storList of storage paths, handy to grab the path if you dont want to hardcode (if you have multiple)I use this to generate a path to a file I want to find, eg \\servername\f$\projectwise\data\dm1111111\dms_workspaceprofA list of all workspace profiles, again I just grab the names from here to compare the [o_workspaceprofileno] against the [o_wspaceprofno] on dms_proj to find which folders have a specific environemnt set if i want to delete it.
I'll upload a few SQL scripts I use for general admin tasks when I can find them.
Yes. please upload some SQL queries if you can find them. That will be great helps.