Working with big databases, consisting of many, many tables (like hundreds or thousands), can be really painful. Recently, this happened to me and I discovered 2 life-hacks that help me survive this hard days.

Filtering tables

You know the struggle when you know a part of a database table name but cannot remember the whole name? How could it be named? BasketPositions, CustomerBasketPositions or maybe PositionsOfBasket? How can you know?! Yeah, it’s the everyday struggle with huge projects!
This is the moment when Microsoft Management Studio comes to the rescue with its lovely ‘Filter’ context menu!

You can filter out tables which name or schema starts with a given word. I cannot believe I haven’t known it before! 😀

Additionally, this filtering can be used also for searching for a View or a Stored Procedure:

Finding the last table where data were modified

Well, sometimes you are too frustrated / in a hurry (or both :P) to spend another hour on analyzing the source code just to discover where this f*cking data are being saved 😉. All you need is just a simple information – which table will save data from this form, you just don’t want to discover all not-concerning-you-at-this-moment business logic?

To solve this problem, I just run my application, submit a form (or do anything needed) that affects the database table I look for and I use SqlServer Management Studio again. This time I run a simple query:

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YOUR_DATABASE_NAME')
ORDER BY last_user_update DESC

This query returns the table name, datetime of update/insert etc.

And that’s it. If you know any other life-hack that makes working with big project less miserable, let me know in a comment!

Photo by Alev Takil on Unsplash