Using QlikView to Document and Manage SQL Server Tables

    Have you ever wanted to make a data dictionary with a list of all tables and all fields in a SQL Server database?

     

    I often do for a few reasons:

    • Quickly determine which fields are in more than 1 table
      • Look at those fields and see what tables they are in
      • Search for a field.  Maybe I want to see all fields like Employee* regardless of what tables they are in.
    • Compare two tables to figure out what fields they have in common so I can figure out how to properly join them

     

    For these reasons I sometimes create a dashboard that simple has all of the tables and fields from a SQL Server database.

     

    Using alternate states you can even select 2 different tables and produce a chart with all the fields and which table(s) they belong to.

     

    This code works in SQL Server and would have to be modified for other RDBMS systems.

     

    let vDBName='myDatabaseName';

    ODBC CONNECT32 TO myDatabaseODBC;

    Tables:
    Load TABLE_NAME as TName,TABLE_SCHEMA & '.' & TABLE_NAME as TABLE_NAME;
    SELECT * FROM $(vDBName).INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
    let vTableCount=NoOfRows('Tables');

    for i=0 to $(vTableCount);
    let vTable=peek('TName',$(i),'Tables');
    let vTable1=peek('TABLE_NAME',$(i),'Tables');

    Fields:
    Load *,'$(vTable1)' & '.' & COLUMN_NAME as QName, '$(vTable1)' as TABLE_NAME;
    SQL SELECT   COLUMN_NAME FROM $(vDBName).INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$(vTable)';

    next i;

     

    In QlikView create 2 Alternate States (Document Settings\General\Alternate States).  You could create just one but it’s easier if we name both states.

     

    Create 2 list boxes that show TABLE_NAME one with the default state and one with the new alternate state.

    Set the listboxes to always 1 selected value.

     

    Create a straight table chart.

     

    Add COLUMN_NAME as the dimension.

    Add =concat(  { [1stState] } distinct [TABLE_NAME])  with a label of Table 1

    Add =concat(  { [2ndState] } distinct [TABLE_NAME])  with a label of Table 2

     

    It is also useful to have a chart that shows Column_Name and Count(TABLE_NAME) and some other list boxes maybe Column_Name and TABLE_Name without state so you can select a field and see all the tables it’s in.