I have about 150 reports generated from SQL statements. These reports are saved independently from the server. Some generated from sprocs, some from table-valued functions, but mostly just SQL statements.

Now the server goes through a big refactoring of tables and sprocs. And I need a way to validate that all the reports are still running, as it is possible the changes will break the reports.

One way to validate that all the reports are valid - run them and lookout for errors.

The problem - some of the reports are very heavy and include aggregation of of many tables with many joins with millions of records.

Is there a way to validate SQL statements without actually executing them?

I'm working with SQL Server 2008 and have ability to apply C# processing to the statements. Ideally I'd like a way to validate the reports often and without much effort, i.e. 10 times a day. So making a schema-only copy of DB is not an option.

  • The real issue here sounds like a lack of a change management system, wherein these reports should be tested in a dev environment where running them wouldn't be an issue. Alternatively you could make modified versions of the reports that only select a few rows to ensure they run. – LowlyDBA Feb 24 '15 at 21:36
  • There are many issues here, never mind the change management and other good practices. But I am where I am and I need to make this work. So you are saying add top 1 after every Select keyword? – trailmax Feb 24 '15 at 21:41
  • That may not apply to all the reports, but that's where I'd start. SELECT 1 FROM ... might prove useful as well. – LowlyDBA Feb 24 '15 at 21:42

There's the table sys.sysdepends which will help with your stored objects.

For embedded SQL you could SET ROWCOUNT for the batch. This will terminate the query after compilation (and hence name resolution and object binding) but before large quantities of data are processed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.