Open Forum

Expand all | Collapse all

Export all tables and table fields into single Excel spreadsheet

  • 1.  Export all tables and table fields into single Excel spreadsheet

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Hi NAVUG Community, hoping to gain some insight on how to accomplish the below for a customer.

    Client is integrating NAV with Quickbase and needs to export all tables and all table fields into a single excel spreadsheet, similar to below:

    NAV Schema


    Quickbase consultants need this schema to move forward with the integration.   Is there a way to export this data in this type of format using either the Development Client, Jet Reports (Table Builder?), powershell or SQL?   An advice is appreciated.


    ------------------------------
    Colin Raden
    System Architect
    Enavate
    Philadelphia PA
    ------------------------------


  • 2.  RE: Export all tables and table fields into single Excel spreadsheet

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Edited by Colin Raden 18 days ago
    I created a custom page which allowed me to access Table No. 2000000041, and that seems to have provided me with all tables and table fields in the database.

    ------------------------------
    Colin Raden
    System Architect
    Enavate
    Philadelphia PA
    ------------------------------



  • 3.  RE: Export all tables and table fields into single Excel spreadsheet

    SILVER CONTRIBUTOR
    Posted 16 days ago
    If this is a one-time deal, you could use Rapidstart - I haven't tried more than about 20 tables exporting to Excel in one shot, though. But if it's a one-time deal, you coul use several rxcel exports then combine them after the fact. Depending on the number of records, however, Rapidstart can be a little slow.


    ------------------------------
    Ron Saritzky
    Generate Life Sciences
    Los Angeles CA
    ------------------------------



  • 4.  RE: Export all tables and table fields into single Excel spreadsheet

    Posted 15 days ago
    If you have access to the database, you could run a query. I use this one myself.

    select
    schema_name(tab.schema_id) as schema_name,
    tab.name as table_name,
    col.column_id,
    col.name as column_name,
    t.name as data_type,
    col.max_length,
    col.precision
    from sys.tables as tab
    inner join sys.columns as col
    on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
    order by schema_name,
    table_name,
    column_id;

    ------------------------------
    Julie DiCesare
    Crown Battery
    Fremont OH
    ------------------------------



  • 5.  RE: Export all tables and table fields into single Excel spreadsheet

    TOP CONTRIBUTOR
    Posted 14 days ago
    here's a version of Julie's that I use. Lets you remove the company name prefix and guid suffix (BC 15+ I think - we went from BC13-BC16 and that was when I first saw the GUID on MS tables)

    DECLARE @CompanyName NVARCHAR(50) = 'CRONUS USA, Inc_$'


    SELECT
    SCHEMA_NAME([tab].[schema_id]) AS [schema_name]
    ,[tab].[name] AS [table_name]
    ,REPLACE([tab].[name],@CompanyName,'') AS [table_name_no_company]
    ,LEFT(REPLACE([tab].[name],@CompanyName,''), LEN(REPLACE([tab].[name],@CompanyName,'')) - 37) AS [table_name_no_company_or_guid]
    ,[col].[column_id]
    ,[col].[name] AS [column_name]
    ,[t].[name] AS [data_type]
    ,[col].[max_length]
    ,[col].[precision]
    FROM
    [sys].[tables] AS [tab]
    INNER JOIN [sys].[columns] AS [col] ON [tab].[object_id] = [col].[object_id]
    LEFT JOIN [sys].[types] AS [t] ON [col].[user_type_id] = [t].[user_type_id]
    WHERE
    [tab].[name] LIKE @CompanyName + '%'
    ORDER BY
    [schema_name]
    ,[table_name]
    ,[col].[column_id];

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 6.  RE: Export all tables and table fields into single Excel spreadsheet

    NAVUG ALL STAR
    Posted 14 days ago
    There is an existing page which lists all the tables and fields.  Page 7702 (Fields) provides the information.  It is not currently searchable and needs to be run from Object Designer or navigate directly to the page from the web client.  Take a look.

    ------------------------------
    David Wiser
    Sr. Solution Consultant
    Tigunia
    2019-2020 Board of Advisors
    BCUG|NAVUG All-Star
    ------------------------------



  • 7.  RE: Export all tables and table fields into single Excel spreadsheet

    TOP CONTRIBUTOR
    Posted 14 hours ago
    Wowzer DavidW that Page 7702 sounds perfect for this need, and one could spin up a webservice for that and allow EXCEL to pull that data set right in right?

    Related export need...
    Is there an object (now) that provides a mapping of all OPTION ID to OPTION Values for the Options tables?   in NAV < v7 we had to create a code unit to get those out of the application for reporting written against the database (or build a  long CASE statement for all the values in every report).

    ------------------------------
    -Bob*
    Bob Stahr
    BTC - Business Technology Consulting  | http://bit.ly/wwwbtc
    bob@stahr.us
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role