August 25, 2009 at 10:46 am
I don't know if they still have the program, but 3-4 years ago I purchased ERStudio, DBArtisan and ChangeManager with SQL Server-only licensing limited to 5 or 10 (can't remember) concurrent server registrations which could be switched out at will. Each product was under $1k, with CM being well under 500 IIRC. It was a perfect scenario for an independent consultant like myself, or for a small SQL Server shop.
I just checked and they have the same deal now: DBArtisan is $1100 + $260 maint for 5 sql server licenses. ChangeManager $400 + 100 (5 server licenses). ERStudio is $1360 + 340 (unlimited servers but single-platform).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2009 at 1:31 pm
If you're using ER/Studio, you can do (and should) do sub-models fairly easily. Some things you may not have found yet.
1) Make sure the Overview window is open: View -> Overview window. This will show you the whole diagram, with the main window at an appropriate zoom level.
2) Make sure the Zoom window is open: View -> Zoom window. You sometimes have to click the mouse in the main window to get it to track, but this will show you what's under the mouse in the main window at 100% zoom level.
3) If possible, have a 2nd monitor to put the above windows on.
4) When you "reverse engineer" the database, make sure you check the boxes to "Infer Primary Keys" and "Infer FKs"; if these are explicitly present in the database it will not infer them.
5) When the reverse engineering is completed, save and print the report! It will come in handy, if nothing else as a check-off sheet.
6) You can nest sub-models in a hierarchy to whittle down the diagram, or to make points about specific linkages.
7) In the Data Model tree-view, right-click on the "Logical" model (to create a top-level sub-model) or any existing sub-model (to create a sub-sub-model) and select "Create Sub-model" from the pop-up menu. Click one or more entities; try pressing the "Select Related Entities" button below to see what you get that way.
8) Alternatively, select a number of entities from a diagram, then right-click on one and select "Create Sub-model" from the pop-up menu.
9) "Edit Sub-model will let you add entities later, or drag them onto the diagram from the Data Model tree-view.
As you work with ER/Studio you will find additional tricks for working with it. (Not the best UI or documentation, but there are work-arounds.)
For adding descriptions, there are macros for "Export Meta Data to Excel ..." and "Export Object Definitions and Notes to Excel ..." along with "Import ... from Excel". I haven't used them in recent versions of ER/Studio, but some of the "Export to Excel" macros required a specific (default) number of worksheets in the Excel workbook to work, otherwise they blew up with a cryptic error message.
David Lathrop
DBA
WA Dept of Health
August 25, 2009 at 1:46 pm
A quick SQL Server 2005 script to list tables with APPROXIMATE row counts:
-- List Tables with Row Counts
-- WARNING: Row counts are APPROXIMATE values!!!
--This uses sysindexes for efficiency, which is not totally accurate.
-- Lists all tables, and their row count, in the current database.
-- SQL Server 2005 version
SELECT CAST( t.[name] AS VARCHAR(50)) AS Table_Name, SUM(p.rows) as Row_Cnt
-- SELECT ( 'exec sp_changeobjectowner ''' + o.name + ''', ''telwin''' ) as Cmd
FROM sys.tables AS t
JOIN sys.indexes AS i
on i.[object_id] = t.[object_id]
JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE i.index_id < 2
AND t.is_ms_shipped = 0
-- and t.[name] like 'TR%'
GROUP BY t.[name]
ORDER BY t.[name]
----------------------------------------------------
SQL 2000 version:
SELECT CAST( o.name AS VARCHAR(50)) AS Table_Name, i.rows as Row_Cnt
-- SELECT ( 'exec sp_changeobjectowner ''' + o.name + ''', ''telwin''' ) as Cmd
FROM sysobjects AS o
JOIN sysindexes AS i
on i.id = o.id
WHERE i.indid < 2
AND o.xtype = 'U'
AND o.name 'dtproperties'
-- and o.name like 'TR%'
ORDER BY o.name
David Lathrop
DBA
WA Dept of Health
August 25, 2009 at 4:48 pm
Personally I like ER Studio. Thanks to this listing, I am checking out Power Architect now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2009 at 5:06 pm
RedGate also has a tool called Dependency tracker that could be of some use.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2009 at 4:39 am
DLathrop (8/25/2009)
If you're using ER/Studio, you can do (and should) do sub-models fairly easily. Some things you may not have found yet.1) Make sure the Overview window is open: View -> Overview window. This will show you the whole diagram, with the main window at an appropriate zoom level.
2) Make sure the Zoom window is open: View -> Zoom window. You sometimes have to click the mouse in the main window to get it to track, but this will show you what's under the mouse in the main window at 100% zoom level.
3) If possible, have a 2nd monitor to put the above windows on.
4) When you "reverse engineer" the database, make sure you check the boxes to "Infer Primary Keys" and "Infer FKs"; if these are explicitly present in the database it will not infer them.
5) When the reverse engineering is completed, save and print the report! It will come in handy, if nothing else as a check-off sheet.
6) You can nest sub-models in a hierarchy to whittle down the diagram, or to make points about specific linkages.
7) In the Data Model tree-view, right-click on the "Logical" model (to create a top-level sub-model) or any existing sub-model (to create a sub-sub-model) and select "Create Sub-model" from the pop-up menu. Click one or more entities; try pressing the "Select Related Entities" button below to see what you get that way.
8) Alternatively, select a number of entities from a diagram, then right-click on one and select "Create Sub-model" from the pop-up menu.
9) "Edit Sub-model will let you add entities later, or drag them onto the diagram from the Data Model tree-view.
As you work with ER/Studio you will find additional tricks for working with it. (Not the best UI or documentation, but there are work-arounds.)
For adding descriptions, there are macros for "Export Meta Data to Excel ..." and "Export Object Definitions and Notes to Excel ..." along with "Import ... from Excel". I haven't used them in recent versions of ER/Studio, but some of the "Export to Excel" macros required a specific (default) number of worksheets in the Excel workbook to work, otherwise they blew up with a cryptic error message.
Yeah,
So sar it looks like either Embarcadero ER/Studio or a rather inexpensive alternative may turn out to be Datanamic. I'll go back through this thread and check some more posts. Thanks to everyone for posting!
Chris
SQL 2005 SP2
Tables 4000+
ERP: Designed strictly for Educational Institutions
Origin: Unidata
September 6, 2009 at 6:52 am
are you sure that you __really__ need that diagram ? are you are going to print it ? 4k tables + views,procs, funcs?
much more usefull are tools like these:
SQL Doc from RedGate: http://www.red-gate.com/products/SQL_Doc/index.htm
ApexSQL Doc from Apex: http://www.apexsql.com/sql_tools_doc.asp
both are real good - first one is much faster, but the second one has more options.
September 6, 2009 at 9:51 am
Chris (8/18/2009)
Hi there everyone,My employer has purchased an ERP with an off the shelf database of over four thousand tables. Even the vendor doesnt have a diagram of it. (So Scary)
Well - not really. After all, what tables are there, used etc is a function of the configuration in an ERP system.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply