4

I have recently started a new job that will include lots of db related tasks. There is no document describing the various db tables/columns. I will need to do some BI and I am extremely stressed out by the thought that nobody can help me find my way around the database. There is no  db dictionary available. The db is huge, with ~1000 tables, some of them have ~800 columns. I already have 5 tasks, and I have no idea where to start. There is 1 contractor that works on optimizing the db, but has no knowledge of the business. Please throw at me some coping strategies. How can I create this db dictionary? Maybe there are tools that I can use? Thank you!

2
  • 1
    What are your 5 tasks? If we know that we'll be able to provide you with better guidance. Jan 9 '17 at 5:21
  • 1
    Also, what is your previous experience with SQL Server and what is your new role? Jan 9 '17 at 15:30
4
  1. Get Visual Studio if you don't have it already.
  2. Get Oracle SQL Developer 4.1.3.
  3. Install the SSDT add-in for visual studio.
  4. Install a local instance of GIT for version control.
  5. Install the MS GIT Plugin for Visual Studio.
  6. Create a SQL Server Database project in Visual Studio for your database.
  7. Make all database modifications using the SQL DB Project going forward.
  8. Use the Datamodeler tool that comes with Oracle SQL Developer to build a relational model of your MS SQL Database so that you can visualize it - yes, this will work, and it's free.

Other Notes:

  • Don't include security roles or users in your SQL Database Project.
  • Do include Linked Servers, and Schema definitions.
  • Getting a handle on this is not going to happen over night, so don't beat yourself up.
  • It would be a lot better to have a remote git repo with a local copy that you replicate - but a local repo is better than nothing, and I suggested it because it's faster to get up and running.
4
  • 1
    How is this supposed to help OP? It sounds like he has larger concerns than just visualizing the data structures... Jan 9 '17 at 5:23
  • 1
    One has to start somewhere - how can visualizing the database, implementing source control, and putting a process around deployments not help OP?
    – Lizzy
    Jan 9 '17 at 14:24
  • 1
    My main concern is that your answer is a very specific set of instructions based on your preference for Oracle SQL Developer and GIT. OP has concerns about coping with the challenges of his/her new role so I think this proposed answer may have been a bit premature without more of an effort to uncover the type of help she/he is actually looking for. Jan 9 '17 at 15:38
  • That's a perfectly valid opinion, however, OP did say, "and I have no idea where to start."; my answer isn't based on my preferences - I don't even like git, but it's easy to setup. The Datamodeler in Oracle SQL Developer is by far the best and most comprehensive free modeling tool I've ever used. DB projects are easy to setup within VS, and are purpose build for DB development and deployment. I do understand your concern, but to be fair I don't see how my suggestions are inadequate - if they're not right for you then don't follow them, and if you have a better suggestion then offer it.
    – Lizzy
    Jan 9 '17 at 15:45
1

YOu can start by running the MS SQL Profiler tool to see which tables are used by the applications that use the server

1

Welcome to IT and the marvellous world of undocumented databases.

Yes, it goes against all good management principles, but you will (if you stay with DBA/BI work) find that this won't be the first undocumented system you come across :-)

Start by talking to your colleagues - get the most important tables documented.

Explore the system using tools like SQLDeveloper.

800 fields is probably excessive for a table - mind you, I once worked on a system with 35,000 (yes, that's thirty five thousand). I would be considering a redesign with that many fields.

I think that you should try and leverage any skills that you have! Do you programme in Java? Use that then - or whatever your language is! What BI tools have you used?

Don't include security roles or users in your SQL Database Project.

I would not be doing this! Make use of the capacities of your database. The database is the ultimate fortress for your data. If that's breached, you're up the creek without a paddle.

Plus, if you put it into the database, no mistakes in external code, no stupid/ignorant programmers can mess it up and you'll likely be able to implement trails/logs. The database is the place to implement data security. By all means, use other techniques (belt and braces), but do it in the DB first!

Your question is a bit broad, so I would (if I were you) get back to us here when you run into specific issues - point back to this post and ask your more specific question.

You should also answer the comments about what the tasks are, and what is your experience with databases/SQL Server?

0
1

Step 1: Don't panic. Before you do anything else, remember. Don't. Panic.

tools: SSMS, Sql Trace, Excel, Notepad, and will power.

First thing I do with any new database is look at the primary and foreign keys for naming patterns. I also check to see if the database is a case specific system or not. Then I run a query to get the INFORMATION_SCHEMA column list and dump it into Excel so that I can sort the column names to look for patterns. Some will be obvious and other's not so much. Sometimes, I'll sort the columns ASC and DESC by name, then table and name, then type and name, then name and type, etc. The main point is to get familiar with the names and types and sizes.

From that point, I update the excel document with notes about what each column looks to be doing. Then, using that data, I start writing functions in excel to set the MS_Description property for each column based on quick descriptions. These steps typically get me about 20%-50% of the way thru the system and, by the end of this point, I've usually got a good grasp of how the person/people that built up the tables do their thing.

For anything I don't have a definition for, I'll write custom select statements to look at the top 10-30 DISTINCT value for specific columns.

This will usually get me to 70% of the way. By this point, I will know if the system was haphazardly thrown together (honestly, most systems are identified just before I create the excel document) or has some pattern to all or part of the system.

Now, I start creating a list of procedures, views, and functions to begin looking at the relationships between objects. I usually open SQL Trace and start it then open up the 'View Dependencies' for one of each type of object ranging from Table to Function to capture what SSMS creates.

Then, I copy those queries out (I actually maintain several queries to use on the go now) then update them based on specific objects so that I can see what is using undocumented items and, randomly, some documented ones.

At that point, it's just brute forcing my way thru the information to describe the database system as a whole.

Granted, this is what works for me. Might not be for you. But, the point is, start somewhere and don't freak out. Identify a piece/table/column and focus on it then go to the next piece, rinse, repeat.

Not the answer you're looking for? Browse other questions tagged or ask your own question.