Assigning tables to module

You can use the SQL query below if you would like to automatically assign tables to a certain module based on their name.

INSERT INTO dbo.tables_modules (
    table_id
    , module_id
    )
SELECT tab.table_id
    , m.module_id
FROM dbo.[tables] tab
INNER JOIN dbo.[databases] d
    ON tab.database_id = d.database_id
LEFT JOIN dbo.modules m
    ON tab.database_id = m.database_id
LEFT JOIN dbo.tables_modules mod_tab
    ON tab.table_id = mod_tab.table_id
        AND m.module_id = mod_tab.module_id
WHERE mod_tab.table_id IS NULL
    AND (
        -- define table rules here
        (tab.[name] LIKE 'hr%'
            OR tab.[schema] = 'HumanResources')
        AND tab.[object_type] = 'TABLE' -- or 'VIEW', or remove to copy both tables and views
        )
    AND
    -- define module rules here
    (m.title = 'Module Title')
    -- remove the clause below if you want to
    -- apply this rule for all documentations in a repository
    AND d.title = 'Documentation title'

Parameters

• Module title - The above SQL query assigns tables to a specific module, you must paste its title into the 'Module title' string.
• Documentation title - If you want to execute the rule for specific documentation within the repository, provide its title in place of 'Documentation title'. If you want to execute it for the entire repository, remove this line.
• Rule - You can define your rule for which tables to assign to a specific module using the SQL where clause and fields name, schema, or any other table field.

See repository database specification

Views

If you want to apply the rule to views, you can change the clause tab.[object_type] = 'TABLE' to tab.[object_type] = 'VIEW' (or remove the clause to assign both tables and views).

Stored procedures and functions

If you want to perform the same for procedures or functions, replace dbo.tables with dbo.procedures and dbo.tables_modules with dbo.procedures_modules.

The object_type column indicates whether it's a function ('FUNCTION') or a stored procedure ('PROCEDURE').