So Rene pointed me to the impact Analysis some time ago that...

[robert fuschetto]

So Rene pointed me to the impact Analysis some time ago that allowed me to see where Master files werebeing usedor for the purpose of cleanup if they were used at all.

We are now faced with a SQL Server cleanup requiring the deletion of some columns and renaming of some as well.

My concern is, what if we use one of the columns in a fex somewhere We know the column will not have reporting impact perse, but it may be physically present, may appear in intermediate HOLD files etc etcI suspect deleting the column or renaming it on the SQL side will cause some issue with its related master file in WFand fexes, when run, may terminate

So is there a simple quick way to check our Domain/WorkSpace areas to see if a given column name ever appears in any fexes out there

[Martin Yergeau]

This does work to search in a SQL Repos (Workspace)

SELECT

T2.PRT_PATH AS 'Location',

T2.OBJNAME AS 'Object Name',

'IBFS:' + T2.PRT_PATH + '/' + T2.OBJNAME AS 'Full IBFS path'

--,T1.OBJ_HANDLE

--,CAST(CAST(T1.BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX)) 'Code'

FROM dbo.WF_CONTENT_REVS T1

INNER JOIN dbo.WF_REPOSOBJ T2

ON T1.OBJ_HANDLE = T2.HANDLE

WHERE UPPER(CAST(CAST(T1.BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX))) LIKE '%rgb%'

--and T2.OBJNAME like '%htm'

ORDER BY 1, 2, 3

[robert fuschetto]

waithow and where would I run this codein a fex somehow

[Martin Yergeau]

Can be (sort of SQL pass through).

Personally I run it from SSMS

[Manoj Chaurasia]

Hi Robert,

I found SYSCOLUM to be helpful for a similar situation. The ALIAS field below gives the column name from the underlying table in the database.

Heres a sample Code. There are many more useful fields in this table, in case you want to explore further.

TABLE FILE SYSCOLUM

PRINT

ALIAS

NAME AS Field,Name

COLTYPE AS Data,Type

ACTUAL AS Format

BY TBTYPE AS Suffix NOPRINT

BY TBNAME AS Table

WHERE TBNAME EQ YOUR_TABLE_NAME

-*WHERE TBTYPE EQ SQLORA

-*WHERE ALIAS NE

ON TABLE PCHOLD FORMAT XLSX

END

-RUN

-EXIT

[robert fuschetto]

Thank you. Can this be run from a fex in some waythats the only access I have. What would the process be Right click, create new procedure etcthanks again.

[Manoj Chaurasia]

Yes, you can run it from a fex.

And as you said, you can right click on a folder and choose Create new Procedure option.

[Manoj Chaurasia]

Robert

You can copy and paste the code provided and it will run (after you fix the curly tick marks), but to create your own your admin will have to copy the syscolum master file from the ibisrv82homecatalog directory into the app path for you to be able to reference it in the GUI.