Data Dictionary from within SQL Server 2000

Question

Data Dictionary from within SQL Server 2000

mcurnutt

Ten Centuries

Points: 1222
More actions
February 7, 2002 at 12:00 am

#78204

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcurnutt/datadictionaryfromwithinsqlserver2000.asp

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

ckempste SSCoach Points: 17983 More actions · Answer 1

Nice article Mindy, very informative.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Antares686 SSC Guru Points: 125444 More actions · Answer 2

I have found this usefull already and have been using it a while. Good article on the subject.

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 3

Nice article. I too tried this last year, but was a little ticked that I had to use a cursor to get this info. This should be queryable for all tables, but ...

Anyway, well written and a useful technique.

Steve Jones

steve@dkranch.net

mcurnutt Ten Centuries Points: 1222 More actions · Answer 4

mcurnutt

Ten Centuries

Points: 1222

February 14, 2002 at 8:01 am

#423825

Thanks for all your comments!

Mindy

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 5

I saw your article only today.

It is very usefull, and practical.

I learned so many new things in your article.

Thanks, Keep it up.

Preethi

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

swampy SSC Rookie Points: 44 More actions · Answer 6

Thankyou - this is exactly the answer I have been looking for!!

bnordberg SSCertifiable Points: 5480 More actions · Answer 7

Great idea. I usually find it better to use the information_schema views so I altered your stored procedure as follows

select

o.[table_name] as 'table_name',

c.ordinal_position as 'column_order',

c.[column_name] as 'column_name',

e.value as 'column_description',

C.DATA_TYPE as Type,

coalesce(numeric_precision,character_maximum_length,0) as Length,

coalesce(numeric_scale,0) as [Decimal Pos],

C.COLUMN_DEFAULT AS [Default]

from information_schema.tables o inner join information_schema.columns c on o.table_name =

c.table_name

left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',

N'user',N'dbo',N'table', @table_name, N'column', null) e on c.column_name = e.objname

where o.table_name = @table_name

Thanks

DirkFrazier Grasshopper Points: 24 More actions · Answer 8

I've been exploring this topic for a few days and noticed that the system table sysproperties contained records identified as MS_Description with <Binary> data in the 'value' column. I wrote the following query which returns these records matched with their corresponding records in sysobjects and syscolumns:

SELECT TOP 100 PERCENT dbo.sysobjects.name AS [Table Name], dbo.syscolumns.name AS [Field Name], dbo.systypes.name AS [Data Type],

dbo.syscolumns.length,

dbo.syscolumns.prec AS [Precision], CONVERT(varchar(500), dbo.sysproperties.[value]) AS Description

FROM

dbo.sysobjects INNER JOIN

dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN

dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND dbo.syscolumns.colid = dbo.sysproperties.smallid INNER JOIN

dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype

GROUP BY dbo.sysobjects.name, dbo.syscolumns.colid, dbo.syscolumns.name, dbo.systypes.name, dbo.syscolumns.length, dbo.syscolumns.prec,

dbo.sysproperties.[value]

ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

I'm pretty new at this stuff, so my question is, is this any different than Mindy's solution or am I missing something?

Peter Brightman SSC Rookie Points: 36 More actions · Answer 9

Article is cool, now i know how to query the data dictionary. However i have created my own SQL statement that i use with MS Query, where i cannot define stored procedures but just SQL statements. Here are two simple statements that queries 1. all tables, 2. all tables with all columns.

1. tables

SELECT sysobjects.name 'Table-name'

FROM sysobjects sysobjects

WHERE (sysobjects.type='U') AND (sysobjects.status>1)

ORDER BY sysobjects.name

2. tables with columns

SELECT sysobjects.name 'Table-name', syscolumns.name 'Column-name', syscolumns.length, syscolumns.xprec, syscolumns.xscale, syscolumns.colid, syscolumns.bitpos, syscolumns.isnullable

FROM syscolumns syscolumns, sysobjects sysobjects

WHERE (sysobjects.type='U') AND (sysobjects.id=syscolumns.id)

ORDER BY sysobjects.name, syscolumns.colid

cheers, Peter

pureeevill Grasshopper Points: 15 More actions · Answer 10

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

Matthew Galbraith SSC Eights! Points: 995 More actions · Answer 11

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

Epaulchalypse Valued Member Points: 65 More actions · Answer 12

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

pgw34 SSC Veteran Points: 228 More actions · Answer 13

pgw34

SSC Veteran

Points: 228

June 1, 2003 at 4:38 am

#459286

excellent

paul warwick

Peter Brightman SSC Rookie Points: 36 More actions · Answer 14

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