Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Data Dictionary from within SQL Server 2000 Expand / Collapse
Author
Message
Posted Monday, December 23, 2002 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2002 12:00 AM
Points: 1, Visits: 1

Well done, Mindy! This will help immensely with the documentation of my current project. 8D





Post #27805
Posted Friday, December 27, 2002 9:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2008 12:28 PM
Points: 49, Visits: 4
Nice article, Mindy.

DirkFrazier, I think your solution is equivalent to Mindy's. The [value] field on the [sysproperties] table is a sql_variant type, so you could store just about anything in there I think, though I haven't tried it yet.

I bet there are some neat tricks you could use this for, like storing default formatting etc... the help file has some ideas for how applications could leverage this. I'm really just getting into XML but I see some potential to map some things that XML does to this property table that otherwise can't be directly stored as part of a database schema.

Yet another reminder to me that I should read up on the system tables regularly to find new tricks. :)


Matthew Galbraith




Post #27806
Posted Thursday, January 2, 2003 6:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 27, 2012 2:56 PM
Points: 43, Visits: 15
i've tended to use oracle-like names for views that have been quite useful. i use a few variations on this but datatype, length and columnorder are useful when it comes to reproducing the formats of adhoc queries

/****** Object: View dbo.all_columns Script Date: 12/11/2001 3:47:11 PM ******/
CREATE view ColumnDesc as
select syscolumns.name columnname,sysobjects.name tablename,sysobjects.id tableid,
systypes.name datatype, syscolumns.length length,syscolumns.colorder columnorder,syscolumns.isnullable isnullable, syscolumns.autoval autoval
,sysproperties.[value] [Description]
from sysobjects,syscolumns,systypes,sysproperties
where sysobjects.xtype='U'
and sysobjects.id=syscolumns.id
and sysobjects.id=sysproperties.id
and syscolumns.id=sysproperties.id
and syscolumns.xtype=systypes.xtype
and sysproperties.type=4
and sysproperties.smallid=syscolumns.colid






Post #27807
Posted Sunday, June 1, 2003 4:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:19 AM
Points: 160, Visits: 161
excellent

paul warwick



paul warwick
Post #27808
Posted Tuesday, June 3, 2003 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 2002 12:00 AM
Points: 2, Visits: 1
well yes, it's fine to query the data dictionary. i have written tools some years ago that did generate c struct headers according to the tables of a database in order to use them along with embedded sql. visual studio's wizard for example creates derived CRecordset classes with data taken from the data dictionary with all the fields as data members of the class. of course this is static stuff, if your tables change, you must recreate your classes. i prefer to use an OODBMS these days, so you can forget about the 40% of your code which is just for mapping.

cheers, Peter




Post #27809
Posted Thursday, July 17, 2003 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2003 12:00 AM
Points: 1, Visits: 1
Great arcticle Mindy!

This was my first use of my SQL Server Central membership and it has been extremely valuable!

I am building a data dictionary for a SQL Server Database and this really "Got Me Off The Ground" so to speak.

I also borrowed code from bnordber to put the columns I wanted into the final product.

Thanks A Million!



Post #27810
Posted Thursday, January 29, 2004 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 20, 2004 10:59 AM
Points: 1, Visits: 1
Great article!
Post #98033
Posted Friday, February 4, 2005 6:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:56 AM
Points: 632, Visits: 234
It is possible to use sp_MSforeachtable in this context. You just need to strip out all the extraneous characters when you pass the name to the function call. For example, contrast the output from these two statements:

-- output as [dbo].[tablename]
exec sp_MSforeachtable 'print ''?'''

-- output as tablename
exec sp_MSforeachtable 'print replace(replace(''?'',''[dbo].['',''''),'']'','''')'




Tony
Post #159678
Posted Friday, February 4, 2005 7:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
Great article!

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #159699
Posted Friday, February 4, 2005 12:04 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
Points: 62, Visits: 3

Great article, but I'm thinking this is only the beginning.  Setting and querying the data is a start, but I would love to see a full solution where a script takes the data dictionary from SQL Server and converts it to a formatted Word Document.  Has anyone done something like this already?

Post #159787
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse