SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Automatically extract information from DBMS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLsearcher
Starting Member

47 Posts

Posted - 06/01/2004 :  10:49:24  Show Profile  Reply with Quote
Dear All

We are tired of generating piles of new documenantation after every change in our databasedesign MANUALLY. So we want to use visio to design and generate our SQL 2k databases. Next we want to extract all our relevant information from our DBMS. And there's the rub. How can I extract this info using a view or running a stored procedure? If you have a better (simpler) idea, please don't hold back.

The information we want is:
COLUMN properties
ColumnName, DataType, Length, AllowNulls, Description, DefaultValue, Identity

TABLE properties
PrimaryKey, ForeignKey, Triggers, Permissions, Dependencies, Relationships, Constraints

DATABASE properties
Login, Maintenance plan, Replication, Publication

Kind regards,
SQL Searcher

PS I allready search the forums for keywords like information_schema.columns, INFORMATION_SCHEMA.TABLES and so on. I got some good hints, but no jackpot.

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 06/01/2004 :  10:56:47  Show Profile  Reply with Quote
Why not just generate SQl scripts for your DB?? Visio has drivers to read these or to connect straight to your DB...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/01/2004 :  11:26:55  Show Profile  Reply with Quote
I guess my question has always been...how does EM generate the script, and how can I duplicate that result in a sproc...



Brett

8-)
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 06/01/2004 :  11:54:43  Show Profile  Reply with Quote
I hesitate to say since you must know this already... but you can use sp_oacreate and SQLDMO to generate table deffinition scripts.

http://www.sqlteam.com/item.asp?ItemID=9093
Go to Top of Page

SQLsearcher
Starting Member

47 Posts

Posted - 06/03/2004 :  06:35:41  Show Profile  Reply with Quote
Dear All

Thank you for your swift reactions and your suggestions. Unfortunately they are not good enough. To explain this, I will give you more information on what I realy want.

When I design a database, I want pictures. I use them to visualize things and to talk to others who are involved. For this I use Visio.
When the database is made, I use Enterprise Manager to generate a script to create the database with all of it's objects.
Meanwhile, programmers are using my database to import and extract information. GUI-programmers are building interfaces. They all need information about the database layout. Yet they don't need (can't read) the EM-script. So I use Excel to map databasefields to GUI-fields. Yet this information is allready in the description of a column.
This is a lot of work. But when I do it correct the first time, I don't have to redo it. Unfortunately I make mistakes, the specs are creeping or whatever. And I have to change the database layout. This impacts all of the documentation. I don't want to rewrite this documentation over and over again.
That's why I want one point of input for all information for the database and a tool to extract the information one the layout someone needs from it. I know I can build the database with Visio and I can let Visio create a script. So I can parse this info, or I can parse the script EM. Or I can build a view. My question is, has anybody some experience on this? Or does there exist a tool that can do it?

Kind regards, SQL Searcher
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/03/2004 :  08:03:44  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
The best tool I know for this is ERWin. If you manage it in ERWin, you only have to do it one time and you can use one tool. You can get everything you need from the following SQL tables guys:

sysobjects
syscolumns
sysproperties (This includes the extended property information like description)
sysindexes
sysforeignkeys

From those three columns, you can "build" the entire diagram structure in a stored procedure format.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Edited by - derrickleggett on 06/03/2004 08:04:22
Go to Top of Page

SQLsearcher
Starting Member

47 Posts

Posted - 06/04/2004 :  08:04:23  Show Profile  Reply with Quote
Hello Derrick

Thank you for your suggestion. I downloaded a trial version of AllFusion ERwin Data Modeler 4.1.4 and I will test it.

Kind regards, SQL Searcher
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000