The talismanic centre-back latched onto the end of a precision cross from Mohamed Diame to head beyond a helpless John Ruddy in the 84th minute.
Minutes later Diame added a goal of his own as West Ham sealed a crucial victory which saw them elevated to 10th in the Premier League table.
Victory over Norwich leaves West Ham unbeaten in their last four top flight outings and with 13 clean sheets, records which Collins believes have boosted team morale.
"I couldn't tell you how long it's been since I scored," he told Sky Sports.
"We are delighted, obviously, it takes us up the league and with teams taking points off each other, the dressing room is over the moon.
"It's been a tough three quarters of the season and we've turned a corner now, it's a good place to be."
Collins' header, which came deep into the second half, was his first Premier League goal in six years the defender was relieved to see the deadlock broken.
"It was about time to be honest," he said.
"It didn't look like it was going to happen. It seemed like one of those games where nothing quite went right for both teams.
"They had a lot of chances, Adrian kept us in the game on three occasions and to pop up with the header is always nice."
With the game goalless and the clock edging towards full time, the Upton Park crowd made sure their voices were heard and Collins claims the tension was clearly felt on the field.
"We were confident it would come but you are a little bit nervous," he admitted.
"I think we get a few moans off the crowd for taking the quick free kick but then [Mohamed] Diame puts the cross in.
"I think that [the quick free kick] is why I scored because I wasn't quite ready to take it."