I think Stewart generally shines as Picard. The only time I have found him lacking is when he plays against a far superior actor, e.g. Ian McKellan.
How is he compared to David Tennant? Has anyone seen Hamlet?
I'm intrigued by your statement. I take from what you've written that you saw Waiting for Godot and have based your view on that. Feel free to correct but if that is the case, one should always remember that the role of Estragon (McKellen) offers so much, much, more to an actor in terms of exposition than that of Vladimir (Stewart).
I was lucky enough to see Stewart as the title character in the Scottish play three times in New York.
Reading the play, Macbeth is clearly mad, bad and dangerous to know. Watching Stewart play him, an audience member is still provided with all the monstrous horror of power gone bad, yet still retains a deep sadness as we witness his loss of humanity.
It was a very moving performance.
As far as Tennant is concerned (and granted that HIS poor Hamlet was out of play for some time given his bad back), it is Patrick who was given numerous acting awards for his role as Claudius.
Once again, he manages to lift from what on paper reads as a woeful, horrible excuse for a man, into something that audiences can sympathise with and in many instances agree with - that he makes a better king than his brother and that he loves Gertrude more than his brother ever could.
He did a similar job in Jacobi's Hamlet 25 years ago, again playing Claudius. It's available on DVD (as is this current version soon - it'll be fun to compare).
From the audience perspective, Stewart makes you FEEL for Claudius. And the fact that he makes you feel for him when you shouldn't, given all that you know about the play on paper, is - at least to me - a thing of wonder.