--    query: The query field provides a query of the top 10 rows, for the given table or view.  
--             The field in returned row will be placed at the beginning of the generated query.  
--             The query will be null when not applicable.
--    schema_name:  The schema_name provides the name of the schema of the object, it is often a dbo.
--    object_name: The object_name provides the name of the object (such as a Tables, Views, Stored Procedure, etc. …).
--    object_description: The object_description provides the description of a given type of object.
--    field_name:  This is the name of the sub object referring to (parameter/field/column) of an object. 
--              If this field or the other field columns are set to null, it indicates that there are no sub objects associated with it.
--    field_id: This is the sub object id for the object.  It denotes the order of the field.  
--              When a field id is 1, it is the first in that given object.
--    field_length:  This indicates the size of the field (data type).
--    field_percision: This indicates the size of the field (data type).
--    field_nullable: This indicates that the field is nullable or not (0: false; 1: true).
--    field_typename: This indicates the data type of a given field.
--    field_descriptor: This provides additional description for parameter sub objects.  
--             It will be set to null when not applicable.


With Object_CTE As (
    SELECT 
        object_name = name, object_id, schema_id,
        object_type = TYPE, object_description = REPLACE (type_desc, '_', ' ') 
    FROM sys.objects
),
Field_CTE As(
    SELECT 
        object_id, field_name = name, field_id = column_id,
        type_id = system_type_id, field_length = max_length,
        field_nullable = is_nullable, field_precision = PRECISION,
        field_direction = NULL 
    FROM sys . columns columns
    UNION 
    SELECT 
        object_id, field_name = name, field_id = parameter_id,
        type_id = system_type_id, field_length = max_length,
        field_nullable = has_default_value, field_precision = PRECISION,
        field_direction
    FROM sys. PARAMETERS
    Cross Apply (
Select field_direction = CASE WHEN PARAMETERS.is_readonly = 1 THEN 'Readonly' ELSE '' END + ' ' + 
CASE WHEN PARAMETERS.is_output = 1 THEN 'Out' ELSE '' END + ' ' + 
CASE WHEN PARAMETERS.is_xml_document = 1 THEN 'Xml Document' ELSE '' END
    ) CrossApply
) ,
Query_CTE As (
SELECT 
        query, object_name, schema_name,
            object_description,
            field_name, field_id, field_length,
            field_precision, field_nullable, field_typename
    FROM Object_CTE
    LEFT JOIN Field_CTE
        ON Field_CTE.object_id = Object_CTE.object_id
    Cross Apply (
        Select 
            query 
                = CASE
                    WHEN object_type IN ('U', 'S', 'V') 
                    THEN 'select top 10 ' + field_name + ', * '
 + 'from [' + Schema_Name(schema_id) + '].[' + object_name + ']' 
                END,
            schema_name 
                = Schema_Name(schema_id),
            field_typename =
                CASE
                    WHEN type_name(type_id) LIKE '%char' THEN type_name(type_id) + CASE
                    WHEN field_length = 1 THEN ''
                    WHEN field_length = - 1 THEN ' (max) '
                    ELSE ' (' + CAST (field_length AS varchar (10)) + ') '
                END
                    ELSE type_name(type_id)
                END + 
                CASE
                    WHEN ISNULL (field_nullable, 1) = 1 THEN ' null '
                    ELSE ''
                END 
    ) CrossApply
)
SELECT *
FROM Query_CTE
ORDER BY object_name, field_id