Document Attributes Environment Attribute value (PW_DOC_DESCRIPTION) from SQL query

Hi Dean,

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?

Thx.

Ritchie

What I've found so far:

dms_tabs (Explaining first as its used below)
A list of your environment tablenames.

dms_proj
All 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_doc
All 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_stor
List 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_workspaceprof
A 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.

What I've found so far:

dms_tabs (Explaining first as its used below)
A list of your environment tablenames.

dms_proj
All 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_doc
All 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_stor
List 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_workspaceprof
A 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.