Need Info for a presentation
-
Short of using a diagram - is there anyway to pull attribute info on all table in a database. I need to show the info to programmers and the CIO - Just trying to make it as easy ...and "pretty" as possible.
Is there any kind of query I could run to get this in perhaps in a table????
I have tried the dirgams but some of the Db have alot of tables and they are hard to manage in diagrams
Thanks in advance
Karen
-
If I were you I will just open the design table and then, select the relationship icon. Then, print screen and paste it to your document or presentation. If you need to show the diagram, just create a new diagram and select only two tables (maybe the ones you need for your presentation) with PK anf FK relationship and print screen and paste it to the presentation or docuemnt.
Just an idea, good luck!
-
What type of information do you need? Usually diagrams are better for showing relationships.
With Visio you can reverse engineer and then add labels. Helps to have a plotter to use.
Steve Jones
-
The info I need is datatypes, description of fields and any other attributes/info we can get.
We re thinking about redesigning all of our DBs. IS EVERYONE SITTING DOWN....We are a new crew in this company and we have realized that NONE of the tables have PK. I know we are shocked too! So of course things are starting to go wrong with the DBs. One Db is over a gig in size!!
We need to see all of the info so we can get a handle on what we need to do to fix this problem. But first we have to see what we are dealing with. We want to hire a DBA to help us but the first think the DBA will want to know is what we have already!!
So that is why I need to get this information.
I would love to use the diagram but some of out Db have over 90 tables and the designer just is hard to use with that many. We do not have a plotter!!
i am greatful for any help I can get
Thanks
Karen
-
Try this query:
select substring(a.table_name,1,18) as Table_Name,
substring(a.column_name,1,30) as Column_Name,
substring(a.data_type,1,8) as Data_Type,
substring(cast(a.character_maximum_length as char),1,6) as Max_Length,
substring(cast(a.numeric_precision as char),1,4) as Num_Precision,
substring(cast(a.numeric_scale as char),1,4) as Scale,
substring(a.column_default,1,20) as Column_Default,
a.is_nullable as Nullable,
b.ordinal_position as Key_Seq
from information_schema.columns a
full outer join information_schema.key_column_usage b
on a.column_name = b.column_name
and a.table_name = b.table_name
where a.table_name in
(select table_name from information_schema.tables
where table_type = 'base table' and table_name <> 'dtproperties')
order by a.table_name, a.ordinal_position
-
Unfortunately, I haven't been able to get the extended properties in a query. The query above will give some stuff, but I think you will need a cursor and loop through all tables and columns can call the extended properties function to get descriptions.
However, if you don't have PKs, I'd doubt anyone took the time to enter descriptions.
A better solution if you have the $$ is to get a tool, Erwin, ER/Studio, even Visio and reverse engineer the database. Then add comments and print out reports.
Steve Jones
-
If you don't have the $$, ICT Database Designer provides the same basic functionality as Viso, but it's free.
-
Thank you all for your comments and a few chuckles...I too will begin smocking crack!
I started playing with Visio, for the first time and I am getting some good information.
I am really stumped by this whole scenerio. This person that created these databases was educated and highly paid??? Boogles the mind!
Thanks again and keep your fingers cross that I begin to understand Visio before next Friday when I have to give the presentation!!
Karen
-
Well let's not jump to conclusions, not having PKs is not an issue, unless you really mean there are no indexes. There is no real difference between a PK and an index. PKs are just included for us old foggie's who grew up believing you had to have a PK, but if create a regular index on the same fields, there is no difference.
I would start diagramming with the included diagrammer. If you need to make temporary changes, create a copy of the DB, ddl only, and connect everything up saving it to the test copy.
It's free, and it works (reasonably well).
klk, MCSE
KlK -
Follow up note.
You can use disgrammer and only include a few tables, creating ... disgrams that have to be pasted together. I do that at times.
You can also dump the DDL and work through that. I will have all of the tables, fields, data types etc.
KlK, MCSE
KlK
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply