December 17, 2003 at 10:43 am
We are in the process of developing a Data Dictionary for our SQL Server databases. Are there any suggestions on how one should proceed with such a task? Thanks!
Carpe diem!
Carpe diem!
December 17, 2003 at 11:41 am
I've tried different products (Embarkadero, FMS, Visio, etc.) none worked well. IF you come up with a way,let us know!!!
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 17, 2003 at 2:24 pm
This might be interesting
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]
December 18, 2003 at 7:41 am
Are you looking for a data dictionary, or a metadata repository? For a data dictionary, you can extract all the information with tools like ERWin and then load a database structure with it. Or write code against the SQL-DMO and load it. For a metadata repository, I would use a combination of tools to do it. I have a structure for a metadata repository that handles business concepts through physical tables, even down to physical hardware (servers).
December 18, 2003 at 8:51 am
We use this method to get a datadictionary and it's free. First you enter the text into each description field then run this stored procedure for every table.
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
Use this stored procedure to get the table names in your database, then just run through each table name until you have all of your descriptions to build your data dictionary.
CREATE PROCEDURE Select_DatabaseDesign
AS
SELECT DISTINCT
db_name()AS TABLE_CATALOG
,user_name(o.uid)AS TABLE_SCHEMA
,o.nameAS TABLE_NAME
,case o.xtype
WHEN 'U' THEN 'Base TABLE'
WHEN 'V' THEN 'VIEW'
WHEN 'P' THEN 'Stored Procedure'
END AS Type,
case o.xtype
WHEN 'U' THEN 1
WHEN 'V' THEN 2
WHEN 'P' THEN 3
ENDAS SortOrder
FROM
sysobjects o
WHERE
o.xtype in ('U', 'P', 'V') and status >= 0
ORDER
BY
SortOrder
GO
Edward M. Sokolove
Edward M. Sokolove
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply