How do I validate SQL syntax in SQL Server 2008 R2

Question

Is there the option to check syntax after I have completed create a query? If so, where can I find it? What does it validate and what does it not validate?

Answer 1

SET FMTONLY  ON 
--Your Query Here

This will validate your objects as well. It will give you the error if you do not have that object present in your database. It will not execute your Query on the database but only parse it and validate syntax and objects.

Answer 2

You can click the Parse query button in Management Studio. It's the blue check mark on the toolbar (you can also use Ctrl + F5):

This only validates syntax, and doesn't check that the objects you've referenced exist, that joins are valid, etc. For example the following parses correctly since deferred resolution assumes that by the time you run the query "for real" the object will exist:

SELECT foo FROM dbo.table_does_not_exist;

This also passes parsing:

SELECT d.foo 
FROM x.dbo.does_not_exist AS d
INNER JOIN sys.objects AS s
ON d.blat = s.bar;

Even though sys.objects exists but does not contain the column bar.

It is essentially the same mechanism that allows you to compile a stored procedure that references objects that don't exist yet (which of course will fail at runtime).