Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Using QlikView to Document and Manage SQL Server Tables

dgreenberg
Contributor III

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.

Tags (1)
Comments
curtchamps
New Contributor

You could achieve the same thing using the script keywords which is a more simple approach.

SQLCOLUMNS;

SQLTABLES;

dgreenberg
Contributor III

Curt that's great, I didn't know that and am used to SQL statements that I often output the results to Excel for other purposes.

Version history
Revision #:
1 of 1
Last update:
‎03-03-2017 03:35 PM
Updated by: