Sampling data from Extender tables

Lets be honest! One of the most confusing aspects of working with Extender is being able to tell where things get stored. Even though all table names are pretty clear and need not too much explanation, the bottom line is people still get confused when trying to figure out what is stored where.

I for once, have spent some time working on a T-SQL script that will show all the data stored in Extender tables. I constantly use this method, because I can clearly see where things are when I need to look around for some way data got stored, without having to go executing SELECT statements one by one on each table.

The method is broken down into two steps: the script that retrieves all the extender tables, producing a formatted SELECT statement, and the script (a result of the former script) which actually displays the data in each table.

GetExtenderTables.sql

-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
SELECT '/* ' + RTRIM(TABLE_NAME)
   + '*/ SELECT ''' + RTRIM(TABLE_NAME) + ''' AS Extender_Table, * FROM '
   + RTRIM(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'EXT%'

NOTE: This query is only supported on Microsoft SQL Server 2005 and 2008.

When executed, the above query will produce the following results:

RetrieveExtenderData.sql

-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
/* EXT00100*/ SELECT 'EXT00100' as Extender_Table, * FROM EXT00100
/* EXT00101*/ SELECT 'EXT00101' as Extender_Table, * FROM EXT00101
/* EXT00102*/ SELECT 'EXT00102' as Extender_Table, * FROM EXT00102
/* EXT00103*/ SELECT 'EXT00103' as Extender_Table, * FROM EXT00103
/* EXT00104*/ SELECT 'EXT00104' as Extender_Table, * FROM EXT00104
/* EXT00150*/ SELECT 'EXT00150' as Extender_Table, * FROM EXT00150
/* EXT00151*/ SELECT 'EXT00151' as Extender_Table, * FROM EXT00151
/* EXT00180*/ SELECT 'EXT00180' as Extender_Table, * FROM EXT00180
/* EXT00181*/ SELECT 'EXT00181' as Extender_Table, * FROM EXT00181
/* EXT00182*/ SELECT 'EXT00182' as Extender_Table, * FROM EXT00182
/* EXT00183*/ SELECT 'EXT00183' as Extender_Table, * FROM EXT00183
/* EXT00184*/ SELECT 'EXT00184' as Extender_Table, * FROM EXT00184
/* EXT00185*/ SELECT 'EXT00185' as Extender_Table, * FROM EXT00185
/* EXT00900*/ SELECT 'EXT00900' as Extender_Table, * FROM EXT00900
/* EXT10200*/ SELECT 'EXT10200' as Extender_Table, * FROM EXT10200
/* EXT30200*/ SELECT 'EXT30200' as Extender_Table, * FROM EXT30200
/* EXT40010*/ SELECT 'EXT40010' as Extender_Table, * FROM EXT40010
/* EXT40011*/ SELECT 'EXT40011' as Extender_Table, * FROM EXT40011
/* EXT40012*/ SELECT 'EXT40012' as Extender_Table, * FROM EXT40012
/* EXT40013*/ SELECT 'EXT40013' as Extender_Table, * FROM EXT40013
/* EXT40040*/ SELECT 'EXT40040' as Extender_Table, * FROM EXT40040
/* EXT40100*/ SELECT 'EXT40100' as Extender_Table, * FROM EXT40100
/* EXT40101*/ SELECT 'EXT40101' as Extender_Table, * FROM EXT40101
/* EXT40102*/ SELECT 'EXT40102' as Extender_Table, * FROM EXT40102
/* EXT40103*/ SELECT 'EXT40103' as Extender_Table, * FROM EXT40103
/* EXT40104*/ SELECT 'EXT40104' as Extender_Table, * FROM EXT40104
/* EXT40105*/ SELECT 'EXT40105' as Extender_Table, * FROM EXT40105
/* EXT40106*/ SELECT 'EXT40106' as Extender_Table, * FROM EXT40106
/* EXT40107*/ SELECT 'EXT40107' as Extender_Table, * FROM EXT40107
/* EXT40108*/ SELECT 'EXT40108' as Extender_Table, * FROM EXT40108
/* EXT40109*/ SELECT 'EXT40109' as Extender_Table, * FROM EXT40109
/* EXT40110*/ SELECT 'EXT40110' as Extender_Table, * FROM EXT40110
/* EXT40111*/ SELECT 'EXT40111' as Extender_Table, * FROM EXT40111
/* EXT40117*/ SELECT 'EXT40117' as Extender_Table, * FROM EXT40117
/* EXT40150*/ SELECT 'EXT40150' as Extender_Table, * FROM EXT40150
/* EXT40151*/ SELECT 'EXT40151' as Extender_Table, * FROM EXT40151
/* EXT40152*/ SELECT 'EXT40152' as Extender_Table, * FROM EXT40152
/* EXT40153*/ SELECT 'EXT40153' as Extender_Table, * FROM EXT40153
/* EXT40154*/ SELECT 'EXT40154' as Extender_Table, * FROM EXT40154
/* EXT40157*/ SELECT 'EXT40157' as Extender_Table, * FROM EXT40157
/* EXT40160*/ SELECT 'EXT40160' as Extender_Table, * FROM EXT40160
/* EXT40162*/ SELECT 'EXT40162' as Extender_Table, * FROM EXT40162
/* EXT40163*/ SELECT 'EXT40163' as Extender_Table, * FROM EXT40163
/* EXT40164*/ SELECT 'EXT40164' as Extender_Table, * FROM EXT40164
/* EXT40170*/ SELECT 'EXT40170' as Extender_Table, * FROM EXT40170
/* EXT40200*/ SELECT 'EXT40200' as Extender_Table, * FROM EXT40200
/* EXT40201*/ SELECT 'EXT40201' as Extender_Table, * FROM EXT40201
/* EXT40400*/ SELECT 'EXT40400' as Extender_Table, * FROM EXT40400
/* EXT40600*/ SELECT 'EXT40600' as Extender_Table, * FROM EXT40600
/* EXT40601*/ SELECT 'EXT40601' as Extender_Table, * FROM EXT40601
/* EXT40602*/ SELECT 'EXT40602' as Extender_Table, * FROM EXT40602
/* EXT40700*/ SELECT 'EXT40700' as Extender_Table, * FROM EXT40700
/* EXT40701*/ SELECT 'EXT40701' as Extender_Table, * FROM EXT40701
/* EXT40702*/ SELECT 'EXT40702' as Extender_Table, * FROM EXT40702
/* EXT40703*/ SELECT 'EXT40703' as Extender_Table, * FROM EXT40703
/* EXT40900*/ SELECT 'EXT40900' as Extender_Table, * FROM EXT40900
/* EXT40901*/ SELECT 'EXT40901' as Extender_Table, * FROM EXT40901
/* EXT43200*/ SELECT 'EXT43200' as Extender_Table, * FROM EXT43200
/* EXT43201*/ SELECT 'EXT43201' as Extender_Table, * FROM EXT43201
/* EXT43202*/ SELECT 'EXT43202' as Extender_Table, * FROM EXT43202
/* EXT43204*/ SELECT 'EXT43204' as Extender_Table, * FROM EXT43204
/* EXT43205*/ SELECT 'EXT43205' as Extender_Table, * FROM EXT43205
/* EXT43400*/ SELECT 'EXT43400' as Extender_Table, * FROM EXT43400
/* EXT43900*/ SELECT 'EXT43900' as Extender_Table, * FROM EXT43900
/* EXT43901*/ SELECT 'EXT43901' as Extender_Table, * FROM EXT43901
/* EXT43902*/ SELECT 'EXT43902' as Extender_Table, * FROM EXT43902
/* EXT44100*/ SELECT 'EXT44100' as Extender_Table, * FROM EXT44100
/* EXT44200*/ SELECT 'EXT44200' as Extender_Table, * FROM EXT44200
/* EXT44300*/ SELECT 'EXT44300' as Extender_Table, * FROM EXT44300
/* EXT45000*/ SELECT 'EXT45000' as Extender_Table, * FROM EXT45000
/* EXT50100*/ SELECT 'EXT50100' as Extender_Table, * FROM EXT50100
/* EXT60100*/ SELECT 'EXT60100' as Extender_Table, * FROM EXT60100

Now, you can now copy and paste the above results in a new query window and once again execute against your company database. Now you should be able to see how the data is stored. What I appreciate about this query (and not because I wrote it :-) ) is the fact that it displays the table name as part of the result set. This allows you to know directly where a row came from.

If your goal is to produce a SQL Server view that will link this data, then check David Musgrave's article Creating SQL Views of Extender Data for more information. However, if you are seeking to do this via Extender itself, then you need to check MVP Victoria Yudin's post on Creating Extender Views.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/