Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Red face Unanswered: sp_addextendedproperty (was "What am I missing here?")

    Hi all,
    I would greatly appreciate a boost with this problem. I'm trying to 'script' the mundane task of adding descriptions to my tables (I know this can be done from Enterprise manager via Diagrams, but I have a huge (did I mention huge?) amount of tables and I really don't want to have to go to the properties of each and every table.

    Now, if I read BOL right I can use sp_addextendedproperty (and here is their example):

    sp_addextendedproperty 'Caption', 'Caption Test Table', 'user', dbo, 'table', TestExProp <--- Does this not add a description to the the table?

    Well, I tried it and No Joy ! When I look at the table properties from the Diagram (w/in Enterprise Manager) I have a nice big EMPTY Description Box.
    I tried changing it from 'Caption' to 'Description' thinking that would work but alas, I sit here and wonder where I went wrong ....

    What have I missed?

    Any input would put me out of my misery and make me a productive individual (at least for today !)

    Schimelcat

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know, I always keep a separate data dictionary, rather than use extended properties. You easily record more meta data than is possible....but I'll go have a look.

    EDIT: I don't think that's the caption you're looking for.

    BOL: Use this to check your table, then go ahead and manually add 1, then check it's properties. I think it might be Comments

    Code:
    CREATE   table T1 (id int , name char (20))
    
    EXEC   sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id
    
    EXEC   sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name
    
    SELECT   *
    FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)
    Last edited by Brett Kaiser; 09-27-05 at 13:32.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not thinking.

    Do this.

    Go design a table. click on the properties. You'll see the description window.

    Add a description, the go save script. It'll show you which one it is....

    Code:
    SET @v = N'Test'
    EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Employees', NULL, NULL
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Alternatively, you can always run a SQL Profiler trace of what you are after. Nifty trick, right after disecting system stored procedures.

  5. #5
    Join Date
    Oct 2003
    Posts
    37

    Talking

    Hi MCrowley,
    I don't suppose you'd care to be a bit more explicit? Sounds intrigueing and I'm always open to learning new things.

    Brett,
    I'm gonna give your suggestion a try as well.

    Thanks for the help!

    Schimelcat

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Fire up profiler, and start a trace of your server (hopefully a test server without a lot of traffic). Use Enterprise Mangler to do whatever thing it was (set up a job, query a table, anything). Once Enterprise Mangler is done, stop the trace, and poke through the queries to see what looks to be what you wanted. EM puts out a lot of garbage, but there will usually be a couple of nuggets in there that you can reuse or reshape to your own purposes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •