Select from a table with no rows returned.

G’day,

I recently ran into a nice feature that I had never encountered before.

Sometimes, when developing or administrating an unfamiliar table we will simply do a “SELECT *” so that we can quickly see the column names.

This is pretty much fine when the table is small.

However in the case where the table contains several million (or billion) records then SSMS will continue to return records to the tool for a long time.

The main way people use to prevent this is simply putting on a where clause that always evaluates to false, so in

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee
WHERE 1=2;
GO

However, did you know that you can set the “FMTONLY” option to ON to accomplish the same thing – like so

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee
GO
SET FMTONLY OFF;
GO

Just a little tip that I thought I’d share.

Have a nice day.

cheers

Martin.

Tags: , ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.