Houston began his career in 1977 with Airdrieonians but made just one senior league appearance and dropped into junior football with Livingston United. In 1979, Houston moved back into the senior game with Albion Rovers and played over 100 league matches over four years, scoring 33 times. Curiously, a similar length of spell at Falkirk yielded another 33 goals and upon leaving in 1986 to join Dumbarton, Houston had made over 250 senior league appearances. After two years with The Sons, Houston returned to Falkirk for three years before a final playing spell with East Stirlingshire. Houston was worshipped by the Falkirk supporters and even had his own supporters club called The Houstie Hardcore.
Upon retiring in 1993, Houston returned to Falkirk for his third spell with the club, although this time in a coaching capacity. Houston was involved in setting up Falkirk's youth academy and went on to work as an SFA Development Officer at Brockville. When manager Jim Jefferies moved to Hearts in 1995, Houston joined him as reserve and youth team manager, becoming caretaker manager when Jefferies left for Bradford City in 2000. Despite being backed by the players for the manager role, Houston had to settle for an assistant manager role when Craig Levein was appointed instead. Houston followed Levein to Leicester City in 2004, where he stayed until Levein and his backroom staff were sacked in January 2006.
Houston was out of football until his appointment as assistant manager to Levein at Dundee United in October 2006. During the summer of 2007, he suffered a bizarre injury by falling off his bike over the handlebars and breaking both wrists. Levein left the club in December 2009 to become manager of the Scottish national team, with Houston taking over as caretaker manager. Despite initially being considered favourite to take over on a permanent basis, Houston announced he would not be applying for the job following a 7–1 defeat against Rangers under his tenure. In the wake of that result, the club tried to appoint then Bohemians manager Pat Fenlon as Levein's successor, but the deal to bring Fenlon to Tannadice broke down after the clubs were unable to agree compensation. After an improved run of form under his management, Houston was appointed United's manager until the end of the 2009–10 season. Houston will also assist Levein with his Scotland duties on a part-time basis. He guided Dundee United to 3rd place in the SPL and the final of the Scottish Cup, where they defeated Ross County 3–0 at Hampden Park on 15 May 2010 to seal their first major trophy for 16 years. On 25 May 2010, Houston accepted a three-year deal to become manager of Dundee United. "I'm absolutely privileged and honoured to be the manager of Dundee United. For me, the hard works starts now," he told BBC Scotland.
In his first full season as manager United in 4th place in the SPL, thereby qualifying for Europe again and reached the quarter final of the Scottish Cup. In the summer of 2011 several key players left the club or were sold including David Goodwillie, Craig Conway and Morgaro Gomis. United's start to the 2011-2012 season was patchy. United won only two of their first 12 league matches and a League Cup defeat by First Division side Falkirk led to reports that a loss in the Terrors' next league game away to Dunfermline would result in Houston being sacked. After a convincing 4-1 win the United board backed Houston, but former manager Craig Levein criticised the board for not publically supporting Houston earlier. Eventually, United's form improved and the club again finished in fourth place, once again qualifying for the Europa League, an achievement which pleased Houston who expressed pride in his players’ performance.
Dundee United and Houston started contract negotiations during the 2012–13 season, but United chairman Stephen Thompson insisted that Houston would have to accept a cut in salary. On 14 January 2013, Dundee United granted Football League Championship club Blackpool permission to speak to Houston about their vacant managerial position. On 17 January 2013, Houston announced he would leave United at the end of the season after rejected a new contract. Houston left the club by mutual consent on 28 January 2013, bringing forward his date of departure. United chairman Stephen Thompson commented that football fans in the United Kingdom were not accustomed to managers pre-announcing their departure.
After leaving Dundee United, Houston joined Celtic as a scout in December 2013. Houston later says his time at Celtic was a positive one for him because they cited "were fantastic to work for."
After eight months at the club, Houston was appointed manager of Falkirk in June 2014, succeeding Gary Holt.