Post reply

noob tsql confusion!

  • October 29, 2011 at 5:36 pm

    #247019

    Hi all,

    trying to learn tsql so thought i would try and make a data dictionary for my class in db design from tsql instead of manually in excel!

    The end result should look like:

    Table | Column | Type | Size | Constraints | PK | FK | Defaults

    Using sys.objects sys.columns and sys.types it looks like i can get everything i need.. but I cant work out how to do it..

    What im thinking i need to do is:

    Check if there's a parent_object_id

    If there is see What the type_desc is

    If its PK then boolean the PK table

    If its F then boolean the FK table

    On the row that's on the parent_object_id

    If anyone has any simple examples of how to work it out .. that would be great...

    I keep getting simple bits done.. but cant see how to put it all together 🙁 (join the col table to the types to get the format for the cols)

    Thanks for any ideas, snippets, help :)..

    S

  • October 29, 2011 at 6:45 pm

    #1400849

    look at this link - might not be exactly what you want, but a good place for yout to start

    http://www.sqlservercentral.com/scripts/Data+Dictionary/72048/

    by Abdullah.Khan

  • October 30, 2011 at 7:16 am

    #1400862

    Hi prvmine,

    Yeah figured there would be some more pro ones out there..

    Trying to use it as a learning process though so going to try and keep my eyes off that one! hehe

    thanks for the link though 🙂

  • October 30, 2011 at 7:26 am

    #1400863

    Exactly, you can pull the example apart see how it works -- comment things out, add new elements -–all while you’re learning.

  • October 30, 2011 at 9:11 am

    #1400867

    prvmine (10/29/2011)

    look at this link - might not be exactly what you want, but a good place for yout to start

    http://www.sqlservercentral.com/scripts/Data+Dictionary/72048/

    by Abdullah.Khan

    Gosh... I wouldn't use that script as an example. Have you actually checked its results? It doesn't return the PK correctly and its a terrible example of how to format code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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