February 12, 2009 at 2:21 am
Hi,
Anybody can help me please...
I need to know and understand the full database structure of my company. At this moment I know a very few table and their relationship.
Iwant to learn whole database structure such that I do not have to ask anybody for the table structure..
There are nearly 400 procedures which i have to know where its being used and which are not.
The table structure also with their constraints and why the data is being stored into it.
This is my First job as a DBA and I have to learn all these...
Please anybody.. any book reference .. anything will help..
Any guidance please
Thanks
Harsha
February 12, 2009 at 3:35 am
A first step would be to get a tool like Visio and reverse engineer to database so you can see the entire (or subset, depending on which tables you select) database schema and the relationships.
Hopefully the stored procedures are named in a way so that it indicates which part if the GUI is calling them.
You shouldn't really be expected to know where each and every stored procedure is called from within the application. We have just over 300 in our app and I could still not tell you where some of them are called from. When I'm stuck, I just bug the developers to get the information I need.
It'll take some time for you to fully understand the system.
Enjoy!
February 12, 2009 at 4:42 am
... and here is when you are going to find out how serious is the IT operation in your Company, go and ask for documentation.
Just tell us which face better describes the answer to your question.
🙂
😀
😉
:w00t:
😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 12, 2009 at 5:06 am
You wont believe it .. there is no documentation for the database. I am expected and been asked to create the documentation...
February 12, 2009 at 5:10 am
harsha.bhagat1 (2/12/2009)
You wont believe it .. there is no documentation for the database. I am expected and been asked to create the documentation...
I think most of us will believe it!
February 12, 2009 at 5:39 am
Now what should i do...
Also If anybody can help me .. in konwing there are some tables where there are description and caption present. I want to know them by a query or a procedure.. can i get that by any chance..
At this moment I am trying fn_listextendedproperty .. I am trying with it.. but it is that i have to write the table name each time.. ..
February 12, 2009 at 5:46 am
As I suggested, if you have Visio or something similar, reverse engineer the schema so you can get a visible feel for the database and its relationships. This was the first thing I did with this job.
Then I started going through the tables and running an sp_depends against the table name to get a list of the stored procedures which were dependent on the object.
I started with what I knew were the key tables (asked the Dev team for that information) and slowly grew my own documatation of what sits where.
You need to interact with the developers as well. You can learn a lot on your own, but you'll get a lot of good feedback from the development team!
Documentating a system which is new to you will take some time. Don't expect to complete this quickly if you want it done correctly. And you'll make a lot of changes too. Just when you think you know what a system does, something will creep out of the woodwork.
February 12, 2009 at 6:39 am
Hi,
I know a bit what system does but not the full functoinality.
I am using the profiler for the flow of data .. but to remeber the whole thing is too tough.... but i am trying.. anything else we can have ..for the same..
Thanks ..
Harsha
February 12, 2009 at 2:04 pm
I would start with the profiler.
Find out when appliaction's hitting the road, which Major proceures are being called and which major tables are being queried.
Then Sp_helptext those major Procedures... get the logic, if not clear, ask your developers (if there is any).
Do Select top 5 * from those Major tables... or Do an ALT+F1 for those tables to get the structures of them.
Select * from sysservers -- to get the linked servers;
Check the job activity monitor to see what jobs are being actively executed and what they do.
February 12, 2009 at 4:11 pm
Step-by-Step Guide to Reverse Engineering a Database in Visio 2000
http://msdn.microsoft.com/en-us/library/aa140264(office.10).aspx
MJ
February 12, 2009 at 4:37 pm
Harsha - It depends on your scope of work, If you are asked to document every functionality then God Save you 🙂 and the reverse engineering will help you as suggested by other forum member..
If your job is to administer systems (Not touching functionality), then you may not have to learn in depth. Auditing will help you.
--Sudhie
February 13, 2009 at 2:52 am
Hi,
I am the only DBA + developer for in database. There are 2 -3 people who knows a bit of the functionality and I do keep aksing them. But its the time I have to learn by myself all these things. So needed help.
I do not have any reverse engineering software to ease my task.
sp_helptext and ALT+F1 will not help me that much... as what the data is and what does that mean it very confusing.. so I was thinking of some guidance to know form other's experience how to know the full functinality and the database structures.
Thanks for all the Help...
Harsha
February 13, 2009 at 4:48 am
I would start by looking at all the tables and working out what each field stores and how it links together. You could create a table with the info in if you wanted so you can refer to it in the future. This SQL is for 2000 but works in 2005
SELECT so.Name AS TableName, sc.Name AS ColumnName, st.Name AS DataType, sc.Length AS FieldLength,
CONVERT(VARCHAR(2000),'') as ColDescription
FROM sysobjects so, syscolumns sc, master..systypes st
WHERE so.id = sc.id
AND st.xtype = sc.xtype
AND so.xtype = 'u'
ORDER BY so.name, sc.colid
February 13, 2009 at 9:18 am
There are some 3rd party apps for documentation that I have considered checking out myself.
Red Gate's Sql Doc 2.0 ($295)
http://www.red-gate.com/products/SQL_Doc/index.htm
and SchemaToDoc ($125)
Both products allow you to download and try them for a limited time before you decide to purchase them.
There probably are others available too.
I currently use a stored proceedure I developed (based upon information I learned here) in Sql 2000 that will give the basic file layout and uses the extended properties to output the basic table structure when run from Query Analyzer. It is pretty sparce but you are welcome to it if you want.
Steve
February 13, 2009 at 9:45 am
BIDocumentor is not bad either.
Either way, you'll still need to document functionality.
I wouldn't mind seeing that stored proc you wrote Steve.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply