Professional Web Applications Themes

sp_lock - Microsoft SQL / MS SQL Server

sp_lock returns an objectid I would like to see the object name, anyone have a nice wrapper for sp_lock that uses the result set returned to do a join on sysobjects to get the name.. ?...

  1. #1

    Default sp_lock

    sp_lock returns an objectid I would like to see the object name, anyone have
    a nice wrapper for sp_lock that uses the result set returned to do a join on
    sysobjects to get the name.. ?


    alien2_51 Guest

  2. #2

    Default Re: sp_lock

    You can't do a simple join on sysobjects, because the objects referred to in
    the sp_lock output can come from any database on the server.
    The only solutions I have seen for this are ones that use row at a time
    processing and dynamic SQL to access the right sysobjects, corresponding to
    the database id that sp_lock refers to.

    Sybase had a cool solution for this, when they enhanced their object_name( )
    function to take a second parameter of a database ID. I am still waiting for
    MS to do this.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "alien2_51" <dan.billown.o.s.p.a.m.monacocoach.com> wrote in message
    news:#phx.gbl... 
    have 
    on 


    Kalen Guest

  3. #3

    Default Re: sp_lock

    download nice procedure from wrox.com,
    http://web.wrox.com/0764543792/4486_sp_dblocks.zip


    "alien2_51" <dan.billown.o.s.p.a.m.monacocoach.com> wrote in message
    news:#phx.gbl... 
    have 
    on 


    pedro Guest

  4. #4

    Default Re: sp_lock

    Just FYI, this procedure will return the locks held for objects in a single
    database.
    So it can do the join with sysobjects in one database only that the original
    poster asked for.
    To get all the details that sp_lock returns with names translated, for every
    database, you could write a wrapper procedure that called this procedure
    once for each database on your server.
    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "pedro" <com> wrote in message
    news:bh177i$jn6$InfoSky.Net... 
    > have [/ref]
    join 
    >
    >[/ref]


    Kalen Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139