This is the question posed by Tim Mitchell over at https://www.timmitchell.net/post/2024/10/01/t-sql-tuesday-179-whats-in-your-data-detective-toolkit.
The context is around what techniques are used when I’m faced with a new environment, or even just a piece of data. And yes, this is something that I have to do quite often. We have customers ask us to analyse their environments, look for opportunities to tune their systems, come up with new reports, build analytics, you name it. Sometimes we have to deal with problems, such as strange errors or corruption, and it’s not always clear what is what inside the database.
Lists of table names don’t always help. Foreign keys can be helpful, but even those are not always present. However, what I find to be the most useful are the modules in the database. The views, functions, and procedures. Even the queries that are in the cache. But the most useful are report definitions.
Why report definitions? Because this shows business logic. A report is probably filtering out data that should be ignored, looking up data from other tables, aliasing the columns, or at least displaying the data with useful labels, and this can give me insight to what is stored where and what means what.
Many systems (not just SAP) have table names that feel almost meaningless. Or that are configured differently from implementation to implementation so that any prior experience doesn’t really help. And then there are those systems that use tables and columns in non-intuitive ways, such as storing information about people in a table where the columns would suggest they’re actually organisations. No matter what’s going on with these obstacles, business reports (assuming they’re correct and current) will always translate the mess into a way that makes sense to the business.
If these are not available, and modules aren’t proving helpful either, then I’m looking at the number of rows in tables, to get a feel for where the action is. I’m looking for columns that indicate when data has been added, to see what “normal activity” does. I’ll look in the plan cache or Query Store (if they’re available) to see what’s getting called, but none of this is as useful as reports which provide business logic.
I know Tim was asking for specific toolkit stuff, and expecting people to mention the standard “I’m looking for issues in your stuff” tools like SQLFrontline by Mitch Wheat (https://mitchwheat.com/sqlfrontline/), which is an excellent tool for pointing out potential issues with a SQL environment. But when I’m trying to understand a database, it’s about finding those translation points between the technical and the business.
