-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[usp_DisplayEntity](@entityName nvarchar(100), @LangId int=2052)

AS

BEGIN

declare @EntityDisplayName nvarchar(100)

SELECT top 1 @EntityDisplayName=l.Label 

     FROM MetadataSchema.Entity e with(nolock)

    LEFT JOIN MetadataSchema.LocalizedLabel l with(nolock) on e.EntityId =l.ObjectId  AND l.ObjectColumnName ='LocalizedName'

WHERE l.Label is not null

    and l.LanguageId =@LangId And ((l.IsManaged =1 and l.OverwriteTime <'2000/01/01' ) or  l.IsManaged =0)

    and e.Name =@entityName

if object_id('tempdb.dbo.#rs') is not null

    drop table #rs

select Distinct @EntityDisplayName as EntityDisplayName, e.Name as EntityName , l.Label ,

   a.PhysicalName,t.Description as AttributeType

    ,case when a.AttributeTypeId='00000000-0000-0000-00AA-110000000030' then   dbo.ufn_MergePicklist (@entityName,a.Name,@LangId)

    when a.AttributeTypeId='00000000-0000-0000-00AA-110000000013' then   dbo.ufn_MergePicklist (@entityName,a.Name,@LangId)  end

       as PicklistValues,

    case when a.AttributeTypeId='00000000-0000-0000-00AA-110000000031' then convert(nvarchar(100), f.Name)

     when a.AttributeTypeId='00000000-0000-0000-00AA-110000000034' then convert(nvarchar(100), f.Name)

     when a.AttributeTypeId='00000000-0000-0000-00AA-110000000035' then convert(nvarchar(100), f.Name) end as ReferencedEntityName,

    a.Name as ColumnName ,

    a.MaxLength,a.AttributeRequiredLevelId as IsRequired,

    a.PrecisionSource,a.PrecisionValue,

    l.Label +'['+a.Name+']' as [Des],

    l.Label +'['+e.Name +'.'+a.Name+']' as [DesFull],

    lower(@entityName ) as EntitylogicalName

    into #rs

from MetadataSchema.Entity e with(nolock),

        MetadataSchema.Attribute a with(nolock)

        left join MetadataSchema.AttributeTypes as t  with(nolock)on a.AttributeTypeId=t.AttributeTypeId

        left join MetadataSchema.Entity f  with(nolock)on a.ReferencedEntityObjectTypeCode=f.ObjectTypeCode

        LEFT JOIN MetadataSchema.LocalizedLabel l with(nolock)

        on a.AttributeId=l.ObjectId

            ANd l.ObjectColumnName ='DisplayName'

        and l.OverwriteTime<'1901/01/01'

WHERE e.Name =@EntityName

    and e.EntityId=a.EntityId

    AND l.Label is not null

    and l.LanguageId=@LangId

order by l.Label

select * from #rs

END

exec usp_DisplayEntity @entityName='product',@LangId='',@type=1