Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLsearcher
Starting Member
47 Posts |
 Posted - 2004-06-01 : 10:49:24
|
| Dear AllWe 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 propertiesColumnName, DataType, Length, AllowNulls, Description, DefaultValue, IdentityTABLE propertiesPrimaryKey, ForeignKey, Triggers, Permissions, Dependencies, Relationships, ConstraintsDATABASE propertiesLogin, Maintenance plan, Replication, PublicationKind regards, SQL SearcherPS 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
3608 Posts |
Posted - 2004-06-01 : 10:56:47
|
| Why not just generate SQl scripts for your DB?? Visio has drivers to read these or to connect straight to your DB... |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-01 : 11:26:55
|
| I guess my question has always been...how does EM generate the script, and how can I duplicate that result in a sproc...Brett8-) |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-01 : 11:54:43
|
| I hesitate to say since you must know this already... but you can use sp_oacreate and SQLDMO to generate table deffinition scripts.[url]http://www.sqlteam.com/item.asp?ItemID=9093[/url] |
|
|
|
SQLsearcher
Starting Member
47 Posts |
Posted - 2004-06-03 : 06:35:41
|
| Dear AllThank 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 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-03 : 08:03:44
|
| 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 syscolumnssysproperties (This includes the extended property information like description)sysindexessysforeignkeysFrom those three columns, you can "build" the entire diagram structure in a stored procedure format.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SQLsearcher
Starting Member
47 Posts |
Posted - 2004-06-04 : 08:04:23
|
| Hello DerrickThank 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 |
|
|
|
|
|
|
|
|
Automatically extract information from DBMS