Announcing sqlcop

“Where’s my fiction,” you demand.

Sorry. I went and wrote about how I want to write fiction and then spend my weekend working on software. That’s awfully inconsistent. Shame on me. Unlike many of my open-source projects, though, I think this one may have merit.

When I got my first contract with Microsoft to help them build their Sarbanes-Oxley tools, I delivered a nice little solution that allowed big-four accounting firm clients to import their engagement data into a common repository for compliance analysis and reporting. When I submitted my code, the Microsoft guys rejected it because it had FxCop warnings. At the time I had only one question to that: what the heck’s FxCop?

When I downloaded it and ran it against my code, I felt truly amazed. What a cool static analysis tool, like lint for .NET. Then came StyleCop which allowed us to perform stylistic analysis. These two tools remain a part of my .NET programming arsenal.

If you don’t use at least one of these tools in your checkin and/or continuous integration processes, then I think you’ve really missed a great opportunity to standardize your code base with “best practices” and “common coding standards.”

sqlcop: FxCop and StyleCop for SQL

In some converstations that I had with some really smart guys, I realized that no such creature exists for SQL analysis. We have loads of tools to check SQL syntax, generate SQL, and import/export SQL, but nothing to enforce “best practices” and “common coding standards” with SQL, stored procedure, and other RDBMS interactivity.

“So,” I thought, “why not just write something to do that?”

You can check it out over at the sqlcop repository on GitHub.

The project doesn’t have a usable build, yet, but the T-SQL parsing is coming along pretty nicely. I should have an engine, a parser, and some rules in the next week or so.

I’ll be writing about it during this next week. If you’re not interested in its development, come on back next Monday; I’ll probably have moved onto another subject at that time. Until that time, I’ll chat about sqlcop’s implementation and interesting features of T-SQL that I come across like…

T-SQL Common Table Expressions (CTE)

I had no idea that these existed, but they allow you to declare self-referencing queries that get stored into a temporary result set similar to a temp table or table variable.

Did you read that: self-referencing queries!

That’s right! You can declare recursive structures in your SQL! I don’t know how well they perform and don’t want to spend the time measuring them, but I had no idea that Microsoft SQL Server supported such a beast. I might even use them, one day.

But, for right now, I’m more interested in parsing their declarations.