USE [SDS_DevSchoolDistrict]
GO

--SELECT * FROM MetadataOfTableFields('AssessmentTestPerformanceLevels')

GO
ALTER FUNCTION [dbo].[MetadataOfTableFields]
(@TableName sysname)
--DECLARE @TableName sysname        SET @TableName = 'Classes'
RETURNS
--DECLARE
@MetadataOfTableFields TABLE
(       ------------------------------------------
        FieldName               sysname,
        FieldOrdinal            int,
        FieldDataType           sysname,
        FieldValueType          sysname,
        FieldInterfaceType      sysname,

        FieldMaximumLength      int,
        FieldIsNumeric          bit,
        FieldIsRequired         bit,
        FieldIsUnique           bit,
        FieldRequiresSelection  bit,
        FieldConstraint         varchar(500),

        FieldIsPrimaryKey       bit,
        FieldIsIdentity         bit,
        FieldIsRepresentative   bit,
        FieldIsForeignKey       bit,
        FieldIsNullable         bit,
        FieldIsComputed         bit,
        FieldIsUpdateable       bit,
        FieldFormula            varchar(500),
        FieldDefaultValue       varchar(500),

        --FieldIsRelativelyUnique   bit DEFAULT 0,
        UniquenessBasisField    sysname NULL DEFAULT(''),
        OrdinalBasisField       sysname NULL DEFAULT(''),

        FieldSelectionSource    sysname,
        FieldSelectionIdField   sysname,
        FieldSelectionDisplayField  sysname

)       ------------------------------------------
AS
BEGIN


        ---------------------------------------------
        DECLARE @PrimaryKeyFields TABLE
        (       -------------------------------------
                PrimaryKeyFieldName sysname
        )       -------------------------------------
        ---------------------------------------------
        INSERT  @PrimaryKeyFields
        SELECT  --i.name AS IndexName,
                --OBJECT_NAME(ic.OBJECT_ID) AS TableName,
                COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
        FROM    sys.indexes AS i
        JOIN    sys.index_columns AS ic
          ON    i.OBJECT_ID = ic.OBJECT_ID
         AND    i.index_id = ic.index_id
         AND    i.is_primary_key = 1
         AND    i.OBJECT_ID = OBJECT_ID(@TableName)
        ---------------------------------------------
        -- SELECT * FROM @PrimaryKeyFields
        ---------------------------------------------



        ---------------------------------------------
        DECLARE @UniqueFields TABLE
        (       -------------------------------------
                UniqueFieldName sysname
        )       -------------------------------------
        ---------------------------------------------
        INSERT  @UniqueFields
        SELECT  Fields.name
        FROM    dbo.sysobjects Objects
        JOIN    dbo.syscolumns Fields
          ON    Objects.id = Fields.id
         AND    Objects.name = @TableName
        JOIN    sysconstraints Constraints
          ON    Fields.Id = Constraints.id -- Constraints.constid
        JOIN    sysobjects ConstraintObjects
          ON    ConstraintObjects.id = Constraints.constid
         AND    ConstraintObjects.xtype IN ('PK', 'UQ')
        JOIN    sysindexes Indexes
          ON    Indexes.id = Fields.Id
         AND    ConstraintObjects.name = Indexes.name
        JOIN    sysindexkeys IndexKeys
          ON    IndexKeys.id = Indexes.id
         AND    IndexKeys.indid = Indexes.indid
         AND    Fields.colid = IndexKeys.colid
        ---------------------------------------------
        -- SELECT * FROM @UniqueFields
        ---------------------------------------------




        ---------------------------------------------
        DECLARE @CheckConstraintFields TABLE
        (       -------------------------------------
                CheckConstraintField    sysname,
                CheckConstraintName     sysname,
                CheckConstraint         varchar(500)
        )       -------------------------------------
        ---------------------------------------------
        INSERT  @CheckConstraintFields
        SELECT  Fields.name, Constraints.Name, Definition
        FROM    sys.objects Objects
        JOIN    sys.columns Fields
          ON    Objects.object_id = Fields.object_id
         AND    Objects.name = 'Persons'--@TableName
        JOIN    sys.check_constraints Constraints
          ON    Fields.object_id = Constraints.parent_object_id
         AND    Fields.column_id = Constraints.parent_column_id
        ---------------------------------------------
        --- SELECT * FROM @CheckConstraintFields
        ---------------------------------------------







        ---------------------------------------------
        DECLARE @ForeignKeyFields TABLE
        (       -------------------------------------
                ForeignKeyFieldName     sysname,
                ForeignKeyParentTable   sysname,
                ParentTableIdField      sysname,
                ParentTableDisplayField sysname
        )       -------------------------------------
        ---------------------------------------------
        INSERT  @ForeignKeyFields
        SELECT  Fields.Name,
                ParentTables.Name,
                ParentColumns.Name,
                ParentColumns2.Name
        FROM    dbo.sysobjects Objects
        JOIN    dbo.syscolumns Fields
          ON    Objects.id = Fields.id
         AND    Objects.name = @TableName
        JOIN    dbo.sysforeignkeys ForeignKeys
          ON    Fields.id = ForeignKeys.fkeyid
         AND    ForeignKeys.fkey = Fields.colid -- colorder
         AND    OBJECTPROPERTY ( ForeignKeys.constid , 'IsForeignKey' ) = 1
        JOIN    sysObjects AS ParentTables
          ON    ParentTables.ID = ForeignKeys.rKeyID
        JOIN    sysColumns AS ParentColumns
          ON    ParentColumns.colorder = ForeignKeys.rKey
         AND    ParentColumns.ID = ParentTables.ID
        JOIN    sysColumns AS ParentColumns2
          ON    ParentColumns2.colorder = 2
         AND    ParentColumns2.ID = ParentTables.ID
        ---------------------------------------------
        -- SELECT * FROM @ForeignKeyFields
        ---------------------------------------------






        ---------------------------------------------
        -- Column Extended Properties
        -- such as OrdinalBasisField and UniquenessBasisField
        ---------------------------------------------
        DECLARE @ColumnExtendedProperties TABLE
        (       -------------------------------------
                FieldName       sysname,
                PropertyName    sysname,
                PropertyValue   sysname
        )       -------------------------------------
        ---------------------------------------------
        INSERT  @ColumnExtendedProperties
        SELECT  --major_id,
                --minor_id,
                --t.name AS TableName,
                c.name AS ColumnName,
                CAST(ep.name as varchar(128)) AS PropertyName,
                CAST(ep.value as varchar(128)) AS PropertyValue
        FROM    sys.extended_properties AS ep
        JOIN    sys.tables AS t
          ON    ep.major_id = t.object_id
         AND    t.name = @TableName
        JOIN    sys.columns AS c
          ON    ep.major_id = c.object_id
         AND    ep.minor_id = c.column_id
        WHERE   class = 1;
        ---------------------------------------------




        ---------------------------------------------
        --
        ---------------------------------------------
        INSERT  @MetadataOfTableFields (
                FieldName,
                FieldOrdinal,
                FieldDataType,
                FieldValueType,
                FieldInterfaceType,

                FieldMaximumLength,
                FieldIsNumeric,
                FieldIsRequired,
                FieldIsUnique,
                FieldRequiresSelection,
                FieldConstraint,

                FieldIsPrimaryKey,
                FieldIsIdentity,
                FieldIsRepresentative,
                FieldisForeignKey,
                FieldIsNullable,
                FieldIsComputed,
                FieldIsUpdateable,
                FieldFormula,
                FieldDefaultValue,

                FieldSelectionSource,
                FieldSelectionIdField,
                FieldSelectionDisplayField

        )
        ---------------------------------------------
        SELECT  DISTINCT TOP 100 PERCENT
                Fields.name,        --AS FieldName,
                Fields.colid,       --AS FieldOrdinal,
                DataTypes.name,     --AS FieldDataType,
                ISNULL(UserDefinedDataTypes.name, ''), -- AS FieldValueType,
                dbo.InterfaceType(DataTypes.name, UserDefinedDataTypes.name, Fields.isnullable, Fields.length),-- AS InterfaceType
                Fields.length,      --AS FieldMaximumLength,
                dbo.DatatypeIsNumeric(DataTypes.name),--AS FieldIsNumeric
                CASE
                    WHEN(Fields.isnullable = 1)
                        THEN 0
                    WHEN(Fields.iscomputed = 1)
                        THEN 0
                    WHEN(PK.PrimaryKeyFieldName IS NOT NULL) -- is a Primary Key
                        THEN 0
                    WHEN(DefaultValues.text IS NOT NULL) -- has a default
                        THEN 0
                    ELSE 1
                END,--AS FieldIsRequired
                CASE
                    WHEN(U.UniqueFieldName IS NULL)
                        THEN 0
                    ELSE 1
                END,--AS FieldIsUnique
                CASE
                    WHEN(FK.ForeignKeyFieldName IS NULL)
                        THEN 0
                    ELSE 1
                END,--AS FieldRequiresSelection,
                ISNULL(CC.CheckConstraint, ''),--AS FieldConstraint,
                CASE
                    WHEN(PK.PrimaryKeyFieldName IS NULL)
                        THEN 0
                    ELSE 1
                END,--AS FieldIsPrimaryKey
                COLUMNPROPERTY(Fields.id, Fields.name, 'IsIdentity'), -- AS FieldIsIdentity,
                CASE
                    WHEN(UserDefinedDataTypes.name = 'RepresentativeValue')
                        THEN 1
                    ELSE 0
                END,--FieldIsRepresentative,
                CASE
                    WHEN(FK.ForeignKeyFieldName IS NULL)
                        THEN 0
                    ELSE 1
                END,--AS FieldisForeignKey
                Fields.isnullable,  --AS FieldIsNullable,
                Fields.iscomputed,  --AS FieldIsComputed,
                CASE
                    WHEN(Fields.iscomputed = 1)
                        THEN 0
                    WHEN(PK.PrimaryKeyFieldName IS NOT NULL) -- is a Primary Key
                        THEN 0
                    ELSE 1
                END,--AS FieldIsUpdateable
                ISNULL(VirtualFields.text, ''), --AS FieldFormula,
                dbo.ExtractDefaultValue(ISNULL(DefaultValues.text, '')),--AS FieldDefaultValue
                ISNULL(FK.ForeignKeyParentTable, ''),
                ISNULL(ParentTableIdField, ''),
                ISNULL(ParentTableDisplayField, '')
        ---------------------------------------------
        FROM    dbo.sysobjects Objects
        JOIN    dbo.syscolumns Fields
          ON    Objects.id = Fields.id
         AND    Objects.name = @TableName
        JOIN    dbo.systypes DataTypes
          ON    Fields.xtype = DataTypes.xtype
         AND    DataTypes.xtype = DataTypes.xusertype
        LEFT    -----------------------------------
        JOIN    dbo.syscomments VirtualFields
          ON    Fields.id = VirtualFields.id
         AND    VirtualFields.number = Fields.colid
         AND    Fields.iscomputed = 1
        LEFT    -----------------------------------
        JOIN    dbo.syscomments DefaultValues
          ON    Fields.cdefault = DefaultValues.id
        LEFT    -----------------------------------
        JOIN    dbo.systypes UserDefinedDataTypes
          ON    Fields.usertype = UserDefinedDataTypes.xusertype

        LEFT    -----------------------------------
        JOIN    @PrimaryKeyFields PK
          ON    Fields.name = PK.PrimaryKeyFieldName
        LEFT    -----------------------------------
        JOIN    @UniqueFields U
          ON    Fields.name = U.UniqueFieldName
        LEFT    -----------------------------------
        JOIN    @ForeignKeyFields FK
          ON    Fields.name = FK.ForeignKeyFieldName
        LEFT    -----------------------------------
        JOIN    @CheckConstraintFields CC
          ON    Fields.name = CC.CheckConstraintField
        ---------------------------------------------
        ORDER BY  Fields.colid
        ---------------------------------------------
        --SELECT * FROM @MetadataOfTableFields
        ---------------------------------------------





        UPDATE  @MetadataOfTableFields
        SET     --FieldIsRelativelyUnique = 1,
                UniquenessBasisField = P.PropertyValue
        FROM    @MetadataOfTableFields F
        JOIN    @ColumnExtendedProperties P
          ON    F.FieldName = P.FieldName
         AND    P.PropertyName =  'UniquenessBasisField'



        UPDATE  @MetadataOfTableFields
        SET     OrdinalBasisField = P.PropertyValue
        FROM    @MetadataOfTableFields F
        JOIN    @ColumnExtendedProperties P
          ON    F.FieldName = P.FieldName
         AND    P.PropertyName =  'OrdinalBasisField'




    RETURN
END