Data Dictionary from within SQL Server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Curnutt Mindy » Data Dictionary from within SQL Server 2000

30 posts, Page 2 of 3

««1 2 3 »»

Data Dictionary from within SQL Server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

pureeevill

Posted Monday, December 23, 2002 4:37 PM

Forum 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

Matthew Galbraith

Posted Friday, December 27, 2002 9:53 AM

SSC 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

Epaulchalypse

Posted Thursday, January 02, 2003 6:26 PM

SSC 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

PAUL WARWICK

Posted Sunday, June 01, 2003 4:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 10:49 AM
Points: 160, Visits: 155

excellent

paul warwick

paul warwick

Post #27808

Peter Brightman

Posted Tuesday, June 03, 2003 4:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 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

GeoFish

Posted Thursday, July 17, 2003 1:49 PM

Forum 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

Philip Tapley

Posted Thursday, January 29, 2004 2:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 20, 2004 10:59 AM
Points: 1, Visits: 1

Great article!

Post #98033

Tony Bater

Posted Friday, February 04, 2005 6:24 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 5:42 AM
Points: 520, Visits: 146

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

Andy Leonard

Posted Friday, February 04, 2005 7:42 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

Great article!

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #159699

JT Lovell

Posted Friday, February 04, 2005 12:04 PM

Valued 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 »

30 posts, Page 2 of 3

««1 2 3 »»

Permissions