Getting the field list returned from an ad-hoc Sql query

So, recently I needed to make an application that allowed a user to enter an arbitrary Sql query, and elsewhere in the UI I needed to display a drop-down with the fields that this arbitrary query returned.

This poses a small problem. It's very simple if the user is doing simple queries, that don't take long to execute. You could just run the query, then, take the first result, and get the list of fields. Well.. This works for simple queries that return small result sets, but we needed to put in queries that potentially return as many as 48 million results, using complex queries including joins between multi-million rowed tables, aggregates, and that sort of thing..

In other words, the queries are slow. Really slow. They create a lot of UI lag when I go to get the field names for the drop down box.

My first attempt was to take the query and wrap it up like this:

SELECT TOP(1) * FROM ( // original query here // ) fieldNamesTable

My thinking was that if I specified that I only wanted the first record it's be really quick, even with a complex query. This is true. It's must faster, but it's still slow. Too slow. A lot of UI lag still remained.

So, my second attempt worked much better. I wrapped the query again, but now it looks like:

SELECT * FROM ( // original query here // ) fieldNamesTable WHERE 1 = 0

Instead of specifying I wanted the first record, I put a phrase in the WHERE clause that will always be false. The Sql Server's query execution engine realizes that, and so it knows that the query will never be able to return data. So it immediately returns with 0 results. But I get the field names!! This is SUPER fast!

Enjoy,
Troy

Code Snippet: SQL FileExists

Today in the course of my work, I came across a situation where some of the files referred to in our SQL database were not actually on disk where we thought they were. This was a largeish database of files (over 10,000), and we thought there might be as many as 1600 files missing, so I didn't want to go through each one manually to find the missing files. That led me to this solution: creating a function in SQL to check if the files exist.

The first method I tried for doing this used an undocumented system stored procedure in MSSQL, called xp_fileexist. The code for that looks like this:

-- using MSSQL built-in stored proc xp_fileexist

CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
BEGIN
DECLARE @i int
EXEC master..xp_fileexist @File, @i out
RETURN @i
END

It's a pretty simple wrapper around the stored-procedure. Implimenting it as a function provides a more versatile tool for querying howver, as shown in this example usage:

--- usage

SELECT *
FROM tbl_FileInformation
WHERE (dbo.FileExists(PathAndFile) = 'True')

Unfortunately, this didn't do the trick for us at that time. MS SQL server apparently cannot, under any circumstances, see mapped drives. All of our data was on a drive called 'P:', which was mapped to a network accessible storage device, that our whole company uses. Not to be discouraged, I thought to myself "Well, perhaps it's just a limitation of the xp_cmdshell options, not SQL server as a whole. May there's another way of finding this out...".

So that led me to write this next function, which uses Scripting.FileSystemObject via the OLE Automation Options. First things first, I needed to run the following commands to enable OLE Automation, to make it possible:

-- configuring for use of scripting object

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

That's the SQL native way, the other option is to use Surface Area Configuartion and enable it via the check-box. Once that was out of the way, I could try out my function...

-- Using the scripting object

CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
BEGIN
declare @objFSys int
declare @i int

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
exec sp_OADestroy @objFSys

return @i
END

But... unfortunately, this gave the same results.

So, the moral of the story? Kids, MS SQL just can't see mapped drives. Give it up now!

If you're lucky enough to have all your data on a drive that's local to the SQL server, and find yourself needing to know if a file you've got referenced still exists, then give these methods a try!

YMMV.