For example look at the Tony Hawk games.
As you can see all the quarter pipes have this random noise and the texture does not seem tiling at all. How can this be achieved??
When it comes to texturing triple AAA games a great bulk of the textures created are for one time use only. Companies will often employee 40 or more artists to create unique textures for every object using a base texture layer on the UVW template. For example, they may create a couple of seamless concrete base textures and then they will apply this to the UVW adding digital grit, noise and dirt for that specific object along with any other necessary detail. After the texture is made it is generally never used again. So when it comes to that half-pipe texture it is likely that the artist made use of a previously created seamless base texture and then added the detail such as the fading red strip and all of the various noise, grit and dirt. Seamless textures are generally used for large scale objects such as terrain, floors, walls, ceilings etc. As variance is not really required and any important variances can be added with transparent grit textures.
With that in mind how would you achieve the same effect? Well first start creating some base seamless textures for all of the different variety of materials you’ll require e.g. Wood, Metal, Concrete etc. After this take your UVW template and tile these textures across your UVW template and then start adding the specific detail required for the model. For example: start adding some fake AO effects and shading to bring out a more 3D effect around any areas you know there will be geometry, flesh out and dig into the texture by making use of masks and start adding implied geometry, add any details such as dirt, decals and grit to the specific areas of the UVW template you want there to be variance and detail. Finally as a little tip I would suggest if you are looking at budget on your texture sizes remember that you can render your UVW and base texture at a larger size such as 1024 or 2048 and then when finished simply down-sample it to a 512x512 or lower. I hope this was helpful. 