Is there any way to check what static/constant value(s) used for which column(s) in views, This needs to be checked in 100s of views so it will be very time consuming if we do it manually by reading a defination of a view.
Similarlly, Another part of question is, can we capture/find what expression/calculation used against computed/dependent column(s) of a view.
Example:
CREATE VIEW dbo.Test
select distinct top 100 percent
NULL AS C1, NULL AS C2, NULL AS C3, NULL AS C4,
NULL AS C5, NULL AS C6, NULL AS C7, NULL AS C8,
NULL AS C9, NULL AS C10, NULL AS C11, NULL AS C12,
NULL AS C13, NULL AS C14, NULL AS C15, NULL AS C16,
'Termination_Category_' + CGSTAT + '_' + RSSTAT as Employee,
CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y'
ELSE 'N' END AS Invol,
'N' AS Inactive,
CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
ELSE 'N' END As Retire,
'Y' AS Manager
from dbo.xyzs
inner join [dbo].[vw_Ref_Test2]
on dbo.xyzs.cgstat = [dbo].[vw_Ref_Test2].[CDCODE]
left join [dbo].[vw_Ref_Test3]
on dbo.xyzs.RSSTAT = [dbo].[vw_Ref_Test3].[CDCODE]
where CGSTAT<>'' or RSSTAT<>''
I need below for the above view:
S.No. View_Name Column_Name Value
1. dbo.Test Employee 'Termination_Category_' + CGSTAT + '_' + RSSTAT
2. dbo.Test Manager Y
3. dbo.Test Retire CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y'
ELSE 'N' END AS Invol,
'N' AS Inactive,
CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
ELSE 'N' END
Any usefull guidance will be really helpful.
Aucun commentaire:
Enregistrer un commentaire