Add Support for Additional Table Metadata to be stored and extracted

[PlagueHO]

• MS_DESCRIPTION if it is stored
• Table collation
• Column collation
• Extended props:

SELECT
       name AS PropertyName,
       value AS PropertyValue
   FROM
       sys.extended_properties
   WHERE
       major_id = OBJECT_ID('dbo.table');

• Triggers:

   SELECT
       name AS TriggerName,
       type_desc AS TriggerType,
       is_disabled AS IsDisabled,
       OBJECT_DEFINITION(object_id) AS TriggerDefinition
   FROM
       sys.triggers
   WHERE
       parent_id = OBJECT_ID('dbo.table');

• Partitioning:

   SELECT
       ps.name AS PartitionScheme,
       pf.name AS PartitionFunction,
       p.partition_number,
       p.rows
   FROM
       sys.partitions p
   JOIN
       sys.partition_schemes ps ON p.partition_id = ps.data_space_id
   JOIN
       sys.partition_functions pf ON ps.function_id = pf.function_id
   WHERE
       p.object_id = OBJECT_ID('dbo.table');
   

• Permissions (not sure this is really useful)

   SELECT
       dp.name AS PrincipalName,
       dp.type_desc AS PrincipalType,
       p.permission_name,
       p.state_desc AS PermissionState
   FROM
       sys.database_permissions p
   JOIN
       sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
   WHERE
       p.major_id = OBJECT_ID('dbo.table');

• Check constraints:

   SELECT
       con.name AS ConstraintName,
       col.name AS ColumnName,
       con.definition AS CheckDefinition
   FROM
       sys.check_constraints con
   JOIN
       sys.columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id
   WHERE
       con.parent_object_id = OBJECT_ID('dbo.tblCinema_Operator');

• Default Constraints:

    SELECT
        con.name AS ConstraintName,
        col.name AS ColumnName,
        con.definition AS DefaultDefinition
    FROM
        sys.default_constraints con
    JOIN
        sys.columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id
    WHERE
        con.parent_object_id = OBJECT_ID('dbo.tblCinema_Operator');