SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Dictionary from within SQL Server 2000


Data Dictionary from within SQL Server 2000

Author
Message
pureeevill
pureeevill
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

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




Matthew Galbraith
Matthew Galbraith
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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. Smile


Matthew Galbraith



Epaulchalypse
Epaulchalypse
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
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



PAUL WARWICK
PAUL WARWICK
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 161
excellent

paul warwick


paul warwick
Peter Brightman
Peter Brightman
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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



GeoFish
GeoFish
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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!



Philip Tapley
Philip Tapley
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Great article!
Tony Bater
Tony Bater
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 261
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
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3469 Visits: 1113
Great article!

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
JT Lovell
JT Lovell
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 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?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search