Get bounty reason in data explorer

Question

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?

Answer 1

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.

For your second question either use ctrl+F in the browser or you can query the views in the information_schema, for example columns:

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.