Finding relevant data in Vision (highly technical)

Let’s just say that you wanted to do some hard coded transformations and key conversions in Vision.

What that means is… Vision doesn’t support the data transformation that you want to do, and you’re wondering if you can do it by hand.

Let’s take an example… transform your WBS3 values to Laborcodes where they belong. Apparently there are a few implementations out there where the conversion from Sema4 or from FMS was done a little bit awkwardly, and the laborcodes got put in to WBS3 instead of laborcodes where they belong.

So then you would ask yourself… well, if I wanted to move all that data back to laborcodes where it belongs so I could use the WBS3 as a project structure element like it was intended, how would I do that?

Well first you would need to fully understand Vision’s use of that particular field… labor code.

from previous posts, I have put scripts down that you could use to find the occurrence of a field inside of all 580+ tables in the Vision database.  But which of those tables are actually relevant for the field you’re looking at?

For example… for laborcode, in a particular database, which tables have that field, and which tables actually have data in that field?

If you want to find that out, you would use this stored procedure that I wrote this afternoon.  Here you go:

/* which dependent tables actually have data ?? */
alter procedure z_pr_pgc_dependent_table_assessor(@findstring varchar(255), @andclause varchar(8000))
— SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

create table #mytables(objectid varchar(255), tablename varchar(255), columnname varchar(255), relevantrowcount bigint)
declare @runsql varchar(8000)
— get relevant table list
set @runsql = ‘
insert #mytables(objectid, tablename, columnname, relevantrowcount)
select t.object_id, as ”table_name”, as ”column_name”, -1 as ”rowcount”
from sys.tables t
inner join sys.all_columns c
on c.object_id = t.object_id
where like ”%’+@findstring+’%” ‘
+ isnull(‘ and ‘+@andclause,”) + ‘
group by t.object_id,,
order by, ‘
print @runsql
declare @objectid varchar(255), @tablename varchar(255), @thiscolumn varchar(255)
select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1
— loop through relevant tables getting relevant row counts
while isnull(@objectid,”) ”
set @runsql =
‘update #mytables
set relevantrowcount = (select count(*) from ‘ + @tablename + ‘ where isnull(‘ + @thiscolumn + ‘,””) ”” )
where tablename = ”’ + @tablename + ”’ and columnname = ”’ + @thiscolumn + ”’ and relevantrowcount = -1 ‘
print @runsql
set @objectid = ”
select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1
select *
from #mytables
order by tablename, columnname
drop table #mytables

Comments or questions for Loren?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s