View: dbo.Privilege_View
View definition
create view dbo.Privilege_View
as
select
privilege_KEY = P.privilege_KEY
, parent__privilege_KEY = P.parent__privilege_KEY
, parent__privilege__description = P1.description
, parent__privilege__parent__privilege_KEY = P1.parent__privilege_KEY
, code = P.code
, description = COALESCE(PD.description,P.description)
, sort = P.sort
, leaf = P.leaf
, tree_level = P.tree_level
, hierarchy = P.hierarchy
, sort_hierarchy = P.sort_hierarchy
from
dbo.Privilege P
left outer join
dbo.Privilege P1 on P1.privilege_KEY = P.parent__privilege_KEY
LEFT OUTER JOIN
dbo.Privilege_Description PD ON
P.privilege_KEY = PD.privilege_KEY AND PD.database_prefix = SUBSTRING(DB_NAME(), 0, CHARINDEX(N'_', DB_NAME()))