I'm trying to understand which bounty reasons get the most usage, but I can't find the bounty reason anywhere in the database schema.
Also, is there any way to do a keyword search on all the table names and fields in the schema?
Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.Sign up to join this community
The bounty reason is now stored in the public dataschema. Let me first show you can get the id for the post notice from the PostHistory
select top 1000 v.postid as [Post Link] , v.creationdate as [vote date] , ph.creationdate as [post notice datetime] , ph.text , ph.comment , v.bountyamount -- v.userid as [User Link] from votes v inner join posthistory ph on ph.postid = v.postid where v.votetypeid = 8 -- bounty start and ph.posthistorytypeid = 33 -- post notice added and v.creationdate = cast(ph.creationdate as date) order by v.creationdate desc
The number in the comment matches with the primary key of the PostNotices table. Here is a query that gives you the number of times a certain bounty reason is chosen:
select name , count(*) [# used] from postnotices pn inner join postnoticetypes pt on pt.id = pn.postnoticetypeid where classid = 2 -- Bounty group by name order by count(*) desc
When run today you'll get the following result:
In the PostNotices table you'll also find the custom text entered by users when they set the bounty.
I leave joining PostHistory and PostNotice as an exercise for the reader.
select table_name , column_name , data_type from information_schema.columns where table_name like '%' + ##keyword:string## + '%' or column_name like '%' + ##keyword:string## + '%'
when you run that for keyword user you get this result:
But maybe you didn't find Database schema documentation for the public data dump and SEDE or the Database Diagram of Stack Exchange model? which might give an better idea of what is possible/available.