I’m making a Microsoft SQL Server NFL database available that contains all play by play data from the 2009-2019 seasons! If you want to be able to do your own analysis or just discover statistical information about players you can start here by downloading a copy of the data an restoring it to a SQL Server database. I suggest using Microsoft SQL Server Express which is free to download and use. The backup of the database can be downloaded here.

NFL Play by Play Database Overview

A PDF of the database diagram is available here. We will cover the data in each table over the content of this post.

NFL Game Data

Included in the database is a table that contains all the games for regular and postseason. Each game record has the score at the end of each quarter.

NFL Game Drive Data

Each NFL Game is composed of drives. The drives are in the Drive table and belong to a game. The pos_team is the team with the ball on that drive. Each drive has a result of the drive such as Punt, Safety, End of Game, etc.

NFL Play by Play Data

The Play table records the result of each play in the game. Each play belongs to a drive and as above each drive to a game. The Play data contains the starting yard line, down, yards to go, etc.

NFL Player Data

The Player table contains the list of NFL players for the 2009-2019 seasons. If a player is no longer active their team will be UNK for unknown and status will be Unkown. The player team will always be their most current team. There is still history if they played for another team. We will get to an example of that in the introduction of the next table Play_Player.

NFL Play_Player Data

Each play can have numerous players involved. The Play_Player table will record the results of each play by player. An example would be if we select the following game (eid), dive and play:

SELECT eid, drive_id, play_id, player_id, team, passing_att, passing_cmp, passing_cmp_air_yds, passing_tds,
receiving_rec, receiving_tar, receiving_tds, receiving_yds, receiving_yac_yds
FROM play_player
WHERE eid = 2016012400 and drive_id=2 and play_id = 467

In this play, Peyton Manning passed to TE Owen Daniels. If you execute the query you will see 2 records of data that show the pass attempt and results.

And to follow up on the previous issue of what team was a player with, we can see Manning was playing for DEN during this play.

NFL Fantasy_data

The fantasy data table that is included in the backup of the database is calculated using full point PPR scoring. In a later post, I will show how to add a column and calculate alternate scoring. All the main elements that go into a fantasy score all stored in the table so it is only a matter of a calculation to get an alternate score.

NFL Agg_Game Table

The Agg_Game table is an aggregate of the play table with fewer fields. It is meant to summarize the result of the game for each side of the ball.

NFL Agg_Play Table

The Agg_Play table is an aggregate of the play_player data. The play_player table has more detailed information than the play table. But the agg_play table summarizes the results for all players for a single play.

NFL Agg_Play_Player

The Agg_Play_player table summarizes the Play_Player data for each game. This will result in a single record for a player per game with details of all the player’s statistics for the single game.

NFL Play by Play Database Summary

That is an overview of the data contained in the database download file. In the next post, I will provide directions on downloading and restoring the NFL Play by Play database to SQL Server Express. Get your database up and running now in just 5 quick steps!