November 6, 2003 at 6:50 pm
I hope I have the term right. I need to generate a nice neat summary of my database, that I can hand off to somebody else, which will enable them to understand all of the structural points of my database (tables, fields, primary keys, relationships, etc.)
I've seen one before; but I'm not sure how to generate one. I bet it's not hard, though
Anybody know how?
November 6, 2003 at 10:26 pm
There are a number of products to do this. FMS Software makes one.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 6, 2003 at 10:56 pm
Consider - DBAction @ http://www.wingenious.com/dbaction/
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
November 7, 2003 at 12:23 am
Have you tried
Might be a good starting point.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 7, 2003 at 3:50 pm
those are all good suggestions- the only problem is, i don't have any available funding to buy software. there must be soem way to do it from within SQL Server, no?
November 10, 2003 at 1:01 am
Try the link I've provided above or directly
http://www.sqlservercentral.com/columnists/mcurnutt/datadictionaryfromwithinsqlserver2000.asp
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 10, 2003 at 2:42 pm
Here's how you do it in SQL Server. First populate your description field in the table design. Then use this stored procedure to retrieve a recordset of the datadictionary. You have to put this SP in each database you have or want to use a data dictionary.
CREATE PROCEDURE Select_DataDictionary @table_name VARCHAR(128)
AS
--Entries in the desciption for that field must be filled in to have a complete data dictionary
SELECT
o.[id] AS 'table_id',
o.[name] AS 'table_name',
--c.colorder as 'column_order',
c.[name] AS 'column_name',
e.value AS 'column_description',
st.name as ColumnDatatype,
convert(int, c.length) as columnLength
FROM
sysobjects o
LEFT JOIN
syscolumns c
ON
o.id = c.id
LEFT JOIN
systypes st
ON
st.xusertype = c.xusertype
LEFT JOIN
::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table',@table_name, N'column',Null) e
ON
c.name = e.objname
WHERE
o.name = @table_name
ORDER BY
c.colorder
GO
Edward M. Sokolove
Edward M. Sokolove
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply