Ice was a Valyrian steel greatsword and an heirloom of House Stark. It was used both in war and on ceremonial occasions by the Lord of Winterfell. Ice had been in the possession of House Stark for generations and was kept in a special scabbard crafted from the pelt of a wolf.
Ice's service to House Stark ended when she was dishonorably used to behead Lord Eddard Stark for supposed treason on the orders of King Joffrey Baratheon. Afterward, the blade was kept as a trophy for a brief time before Tywin Lannister had it melted down and reforged for House Lannister. The two swords that resulted were named Widow's Wail and Oathkeeper; while the former remains with House Lannister, the latter has returned to House Stark's service as the weapon of Brienne of Tarth. Ice has now been supplanted as the ancestral weapon of House Stark by Longclaw, the former weapon of House Mormont.
Eddard Stark uses Ice to behead Will, a deserter from the Night's Watch. Theon Greyjoy holds the scabbard, as Eddard draws the blade. He holds the sword before him as he passes the sentence. Later, Eddard cleans the sword in the godswood at Winterfell. 
When Ned Stark is accused of treason, Ser Ilyn Payne, the King's Justice, is ordered to kill him by King Joffrey. In a show of twisted irony, Ice is the weapon used to kill Ned and is kept in King's Landing afterward as a trophy.
Ice is melted down on Tywin Lannister's orders by a Volantene blacksmith brought to King's Landing to make Jaime Lannister a new sword. However, due to Ice's substantial size, there's enough Valyrian steel to make two longswords. When Jaime asks about the source of the Valyrian steel, Tywin simply replies that "the previous owner no longer needed it." Tywin Lannister also callously burns Ice's wolf-pelt scabbard as an even further gesture of spite and dishonor towards the Starks.
Jaime later gives his yet-unnamed blade to Brienne of Tarth to aid her in her task of rescuing Sansa Stark. He reasons that it is appropriate for Ned Stark's daughter to be protected with a sword forged from her father's own sword. Brienne chooses the name "Oathkeeper" to honor the oath she swore to Catelyn Stark.
In a way, Ice returns to the service of the Starks, when Brienne of Tarth swears her service to Sansa Stark, laying Oathkeeper at her feet. The fate of the second blade, Widow's Wail, has not been revealed.
Jaime Lannister is seen wearing Widow's Wail after returning from the Riverlands. After the Sack of Highgarden, Jaime meets with Lady Olenna Tyrell in her chambers and she points out the sword he wears as formerly belonging to Joffrey, not that he ever used it. She asks what it was he named it, and when told, she remarks that he "really was a cunt, wasn't he?" Olenna then asks if the sword will be used to kill her, but it is poison (provided by Jaime) that does the job.
In the books
In the A Song of Ice and Fire novels, Ice is a huge and impressive weapon forged out of Valyrian steel and has been in the family's possession for over four centuries. The name of the blade however, is far more ancient. In the book "A Game of Thrones", Bran described the sword "as wide across as a man’s hand, and taller even than Robb". As a Valyrian-forged weapon, it never needs sharpening. The blade has a dark and smokey appearance (instead of the appearance of common steel in the TV series). Ned uses Ice to execute the Night's Watch deserter Gared and also to sacrifice Lady. After his father's execution, among the demands made by Robb Stark in exchange for peace was that the sword be returned to House Stark.
In the books, it is actually the Qohorik blacksmith Tobho Mott who reforges Ice into two smaller swords at Tywin's order. The Free City of Qohor is famous for its blacksmiths, who still know how to reforge Valyrian steel (but not create new Valyrian steel outright). Thus while the knowledge of reforging it is rare, it isn't limited to only three living smiths, as Tywin says in the TV series.
For unknown reasons, Mott was unable to forge the two new swords in crimson as Tywin ordered, no matter how much he tried, but in mixed colors of red and the original grey.
When Joffrey is presented with a new Valyrian steel sword by Tywin at his wedding feast (which he names "Widow's Wail"), Tyrion realizes that Tywin has had Ice melted down and re-forged. He regrets not returning the sword to the Starks when it was requested. This is reflected somewhat in the series, as Sansa observes the sword with a grim look, having realized where it came from.