Were you completely unaware of your BIL's shortcomings before this weekend or have there been previous occasions when you found his behaviour lacking, even if only in common courtesy?
From what you've observed, your BIL is emotionally and verbally abusive and the fact that he has your dsis and the dc walking on eggshells indicates that he is also controlling them by his behaviour which, I suspect, is deliberate in that it's an intrinsic part of his nature which may, or may not, be adversely affected by the amount of skunk he inhales.
How old are your dns? Do they appear to be scared of him when he shouts at them, and do you know whether they are achieving or if they exhibit any behaviour problems in school?
Your dsis may have been minimising because she was embarrassed that he was behaving in this manner in front of you but the fact that he did so, and was rude to wait staff, suggests that he has no boundaries and his abuse could extend to physical in the privacy of his/your dsis's home.
Unfortunately it's a case of leading a horse to water but not being able to make it drink in that there's nothing you will be able to do for your dsis until she comes to her own realisation about her h and begins to understand the negative influence he's having on the dc.
Does your dsis live near you and do you meet up with her alone on occasion? If so, I suggest you wait until you see her face to face to voice your concerns in as non-judgemental a manner as you're able to summon up on the day, but it is OK to tell her outright that she and the dc deserve a lot more from him than they're currently getting and that for you, and many other women, having an h who behaves the way he does would be as unacceptable as it is intolerable.
If you don't meet face to face I would suggest yiou wait a few days before giving her a call and ascertaining whether BIL is around before raising your concerns, perhaps by saying that you hadn't realised the extent to which she and the dc have to pander to his wishes and how difficult it must be for her to make so many compromises to accomodate his behaviour.
The more sympathetic you are able to be, the more she may confide in you and I wouldn't be at all surprised if there's a catalogue of various abuses which are ongoing, including financial if he's funding his addiction from money that could be more profitably spent on the dc/the household.
I suggest you buy your dsis a copy of 'Why Does He Do That' by Lundy Bancroft and also find her nearest Women's Aid service here //www.womensaid.org.uk/domestic-abuse-directory/ and either give her the number or make a note of it in the event that he does something that bursts her bubble.
I'm sure you're tempted to rush in like a caped crusader but slow and sure is the only way that may win the day, and I would further suggest that you be sure to have conversations with your dsis in which you don't make any reference to the skunkhead elephant in the room