Where to start?

Over the years I have seen many consultants starting to panic when confronted with an unknown Database. And yes, it can be quite scary to start, especially when there are hunderds of Tables in the Database.

Sometimes there is someone who can help you get started. Other times there is documentation. And if it is a Database that I desgigned, it should be self-explanatory and easy to understand.

But you are not always that lucky. Once I had to do a conversion of a Custom Database which turned out to be a disaster. The Tables were named A1 to A9, then B1 to B9. And when they ran out, they started using AA to AZ, BA to BZ and so further. To make it worse, they used the same structure for the Column Names. And since the company it belonged to no longer paid for support, there was no access to any documentation or a Service Desk. Good luck and have a good day Sir!

Use the Usage Stats

If the Application for the Database is still in use, there is a good option which can give you an indication of which Table was last read or updated. For this you can use the System View sys.dm_db_index_usage_stats:

USE [DBNAME]

select object_name(object_id) as TableName,
last_user_update, last_user_seek,
last_user_scan, last_user_lookup
from sys.dm_db_index_usage_stats
where database_id = db_id('[DBNAME]')

That is, if you are lucky that the application is still up and running so you can ask someone to open up a Screen with what you are looking for. If not, you still might have some luck if the SQL Server was not rebooted since the Application was last used. That is because rebooting the SQL Server erases the Index Usage Stats.

If you want to save the Usage Stats as you found them and prevent them from being erased when there is a reboot of the Server, use this one:

USE [DBNAME]

select object_name(object_id) as TableName,
last_user_update, last_user_seek,
last_user_scan, last_user_lookup
into UsageStats_BackUp
from sys.dm_db_index_usage_stats -- Table will be created
where database_id = db_id('[DBNAME]')

From now on you can just as easily use the UsageStats_BackUp Table. Sometimes I do more backups of them using UsageStats_yyyyMMdd as the Table name, with yyyyMMdd as the Year, Month and Date in the name.

Check Sysobjects

After that, or when the Usage Stats are enpty I would start looking at Sysobjects. This is a System Table which is present in every SQL Server Database containing all the available Tables, Views, Procedures, Triggers and Functions. When the xType equals U, it is a User Defined (not a System) Table. Knowing this, you can check for the number of records in each Table to get an indication of their content using Dynamic SQL to count the Rows:

declare @Table nvarchar(200)
declare @SQL nvarchar(500)
declare @Count int

declare cTests cursor for
select Name
from sysobjects
where xTYpe = 'U'
order by Name

open cTests
fetch next from cTests into @Table
while @@FETCH_STATUS = 0
begin
  set @SQL = 'select @cnt = count(*) from ' + @Table

  exec sp_executesql @sql,
  N'@cnt int output',
  @cnt = @Count output

  print concat(@Table, ': ', @Count)

  fetch next from cTests into @Table
end

close cTests
deallocate cTests

Go for Indexed Tables

Typically Tables that are often used by the Application will have the necessary Indexes. So while this is not a law, Tables with more Indexes are probably used more than Tables without Indexes. Knowing this, you can use the sys.Indexes to check which Tables have the most Indexes:

select top 50
count(*) as Indexes,
so.Name
from sys.indexes i
inner join sysobjects so on i.object_id = so.id
where user_name(objectproperty(i.object_id, 'OwnerId')) NOT LIKE 'sys%'
group by so.name
order by count(*) desc

Build In Tools

Of course in SQL Server Management Studio you have the Object Explorer that can also help you. And when the Application is still running on the Database, you can also use the SQL Profiler which is available under the Tools menu. The Profiler will give you an overview of every query that is executed against the Database. That is why it is crucial the Application is still running.

Find a Value

So, let's say you you are working on an Exact Synergy Database for the first time and you have found out that the Requests are stored in a Table called Absences. In that Table you have found a Column called CustomerID.

But which Table is used to store the Customers? A good guess would be a Table called Customers. But alas, there is no such Table. You could look in the Object Explorer and see if there is a Foreign Key that tells you to which Table this Column might point. But alas, Exact does not use Foreign Keys.

So what do we know?

There must be a Table
Obviously there must be a Table with the Customer information.
It has a Column with that Value
If you grab the Value of a CustomerID from Absences, there must be a Column with that Value in it.
Just one Row
If there are more than 1 Rows containing that Value, it is not likely to be the Table you are looking for. Typically it should be a unique value.
Not a nullable Column
Most likely it is a Key Column and those should all have a Value. So a nullable Column is not a likely candidate.

Knowing this and a bit of Dynamic SQL we can search through the Database:

-- Declare a Variables
declare @ID uniqueidentifier = '959F76A8-09B2-4A67-A1A6-0D37CA811AF2'
declare @Table nvarchar(200)
declare @Column nvarchar(200)
declare @SQL nvarchar(1000)
declare @Count int

set nocount on

declare cTests cursor for
select so.Name as TableName,
sc.Name as ColumnName
from syscolumns sc
inner join sysobjects so on sc.ID = so.ID
where sc.xusertype = 36
and so.xtype = 'U'
and sc.isnullable = 0

open cTests
fetch next from cTests into @Table, @Column
while @@FETCH_STATUS = 0
begin
  set @SQL = concat(
    'select @cnt = count(*) from ',
    @Table,
    ' where ',
    @Column,
    ' = ''',
    @ID,
    ''''
  )

  exec sp_executesql @sql,
  N'@cnt int output',
  @cnt = @Count output

  if isnull(@Count, 0) = 1
  begin
    print @SQL
    print concat(@Table, ', ', @Column, ': ', isnull(@count, 0))
  end

  fetch next from cTests into @Table, @Column
  end

close cTests
deallocate cTests

Using this Query you might find more than one Table that fits the criteria. But usualy just a few. Using the names you found, it is an easy thing to check those Tables and find precisely the one you are looking for.

Don't be scared

Do not panic when you have to work on a Database you have never seen before. You just have to remember a few things.

Only 5 to 10%

Yes, it can be scary to see a Database like an Exact Database for the first time with about 500 tables. But over the years I worked on Exact Databases, most of the times it is on about 20 tot 30 Tables over and over again. When building Financial Reports it is even less than that. The same goes for many other Databases as well.

Developers are lazy, a.k.a. efficient. Most of the Tables are for supporting information. The number of Tables to support the Core Functionality is in general not that high.

Check for Views

Use the Object Explorer to see if there are any Views in the Database. Often you might find Views for common use cases. Those might not be exactly what you are looking for, but they can give you insights on which Tables are relevant and how they are related to each other.

Experiment

As long as you are not creating, updating or deleting Rows, you are completely in the safe zone. Worst case you throw off the Usage Stats, but by now you know how to back them up, right?

Just be sure to never use a Select * so you will not be surprised by an enormous amount of Rows returned. Always use a Select top 10 * or something like that.

But even that one might just result in a long running query that needs to be canceled. No harm done here, ok?

That's it for Today!

As you can see there is no need for panic when you encounter an unknown Database. Yes, it can be a bit of a challenge when there is no documentation or help. And yes, you will need some time to get familiar with it. But it is far from impossible.

So go ahead, explore new territories. There is much more to be discovered!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!