Data Dictionary from within SQL Server 2000

  • mcurnutt

    Ten Centuries

    Points: 1222

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

  • ckempste

    SSCoach

    Points: 17983

    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

    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: 715053

    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

    Thanks for all your comments!

    Mindy

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    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: 34

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

  • bnordberg

    SSCertifiable

    Points: 5379

    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

    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

    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

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

  • Matthew Galbraith

    SSC Eights!

    Points: 995

    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

    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

    excellent

    paul warwick


    paul warwick

  • Peter Brightman

    SSC Rookie

    Points: 36

    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

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

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