Rename All Files in a Directory with NAnt

It’s a pretty simple thing to do but I had to spend a few minutes today to figure out how to accomplish it. So here is a quick build file to rename all .sql files in a specified directory (relative to the script run directory) to .txt. Funny enough, this scripts gets called by my automation program before it uploads any SQL scripts to my blog. There is nothing to it and there are comments so here goes:

<project name="renameSqlToText" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
 <!-- The relative path to the directory where the files are located -->
 <property name="sqlDirectory" value="..\downloads\sql" />

 <target name="run">
  <!-- For each file in the directory -->
  <foreach item="File" in="${sqlDirectory}" property="fileName">
   <!-- If the file has the .sql extension -->
   <if test="${string::to-lower(path::get-extension(fileName)) == '.sql'}">
    <!-- Rename the file to .txt extension -->
    <move file="${fileName}" tofile="${path::combine(path::get-directory-name(fileName), path::get-file-name-without-extension(fileName) + '.txt')}" />
   </if>
  </foreach>
 </target>
</project>

Downloads

http://tech-cats.net/blog/nantScripts/renameSqlToText.build

Silly Little SQL Server Information Schema Helpers

If you work with SQL Server like I do, it is often necessary to drop functions and/or procedures through T-SQL. There are two ways to do this:

1. Using the sysobjects built-in to SQL Server
2. Using the information schema

Microsoft says they do not recommend the sysobjects way since things might change in future version of SQL Server. Regardless, just for reference, here is how to drop a view if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[listVideos]') and objectproperty(id, N'IsView') = 1)
drop view [dbo].[listVideos]
go

Doing the same with a stored procedure is not really much different:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addEncoding]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure dbo.addEncoding
go

Now for the more recommended way, using the information schema:

Dropping a view:

if exists (select * from information_schema.views where table_name = 'listVideos')
  drop view dbo.listVideos
go

Dropping a stored procedure:

if exists (select * from information_schema.routines where routine_name = 'addEncoding' and routine_type = 'procedure')
  drop procedure dbo.addEncoding
go

That is all good and swell, but if you are like me, you might hate to write that long SQL statement every time, so I thought I would shorten it a bit. Here is what I came up with for querying the information schema for procedures and functions. The same can be easily applied to views and tables.

First, we need a single function to check if a procedure or a function exists. I called it “routineExists”. You can download it as a text file from http://tech-cats.net/blog/downloads/sql/routineExistsFunction.txt. Here is the script to create it in your database:

if exists (select * from information_schema.routines where routine_name = 'routineExists' and routine_type = 'function')
	drop function dbo.routineExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @routineName varchar(255)
 - The name of the procedure or function

 @routineType varchar(25)
 - The type of routine (usually ''procudure'' or ''function''

Usage:
 select dbo.routineExists(''getCopyInformation'', ''procedure'')
 select dbo.routineExists(''getCopyInformation'', ''function'')
 if (select dbo.routineExists(''getCopyInformation'', ''procedure'')) = 1 print ''exists''

Returns:
 0 if the routine does not exist
 1 if the routine exists

Description:
 Checks the information schema view for the existance of the routine name passed in
*/
create function dbo.routineExists(@routineName varchar(255), @routineType varchar(25))
	returns bit
as
begin
	declare @routineExists bit
	set @routineExists = 0

	if exists (
		select	routine_name
		from		information_schema.routines
		where		routine_name = @routineName
						and
						routine_type = @routineType
	) set @routineExists = 1
	else set @routineExists = 0

	return @routineExists
end
'

The usage is quite simple:

-- Does the stored procedure 'getCopyInformation' exists, will return 0 or 1
select dbo.routineExists('getCopyInformation', 'procedure')

-- Does the function 'getCopyInformation' exists, will return 0 or 1
select dbo.routineExists('getCopyInformation', 'function')

-- If the procedure 'getCopyInformation' exists, print 'exists'
if (select dbo.routineExists('getCopyInformation', 'procedure')) = 1 print 'exists'

Better but still not quite satisfactory. Next step is wrap the "routineExists” function in individual functions, one for procedures and one for functions.

Let’s start with the procedures one. You can download it from http://tech-cats.net/blog/downloads/sql/procedureExistsFunction.txt. I creatively called it “procedureExists”:

if (select dbo.routineExists('procedureExists', 'function')) = 1 drop function dbo.procedureExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @procedureName varchar(255)
 - The name of the procedure

Usage:
 select dbo.procedureExists(''getCopyInformation'')
 if (select dbo.procedureExists(''getCopyInformation'')) = 1 print ''exists''

Returns:
 0 if the procedure does not exist
 1 if the procedure exists

Description:
 A wrapper for the dbo.routineExists that explicetly checks
 for the existance of the procedure name passed in
*/
create function dbo.procedureExists(@procedureName varchar(255))
	returns bit
as
begin
	return (select dbo.routineExists(@procedureName, ''procedure''))
end
'

Usage is pretty much the same as the “routineExists” function without the “routineType” parameter:

-- Does the stored procedure 'getCopyInformation' exists, will return 0 or 1
select dbo.procedureExists('getCopyInformation')

-- If the procedure 'getCopyInformation' exists, print 'exists'
if (select dbo.procedureExists('getCopyInformation')) = 1 print 'exists'

The function one is called “functionExists”. You can download it from http://tech-cats.net/blog/downloads/sql/functionExistsFunction.txt.

if (select dbo.routineExists('functionExists', 'function')) = 1 drop function dbo.functionExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @functionName varchar(255)
 - The name of the function

Usage:
 select dbo.functionExists(''getCopyInformation'')
 if (select dbo.functionExists(''getCopyInformation'')) = 1 print ''exists''

Returns:
 0 if the function does not exist
 1 if the function exists

Description:
 A wrapper for the dbo.routineExists that explicetly checks
 for the existance of the function name passed in
*/
create function dbo.functionExists(@functionName varchar(255))
	returns bit
as
begin
	return (select dbo.routineExists(@functionName, ''function''))
end
'

Example Usage:

-- Does the function 'listToTable' exists, will return 0 or 1
select dbo.functionExists('listToTable')

-- If the function 'getCopyInformation' exists, print 'exists'
if (select dbo.functionExists('listToTable')) = 1 print 'exists'

Downloads

http://tech-cats.net/blog/downloads/sql/routineExistsFunction.txt
http://tech-cats.net/blog/downloads/sql/procedureExistsFunction.txt
http://tech-cats.net/blog/downloads/sql/functionExistsFunction.txt

Using "select in" with SubSonic

While looking for SubSonic related information on something totally unrelated, I came across a post about trying to use the T-SQL "in" keyword to build a SubSonic collection at http://www.vitaminzproductions.com/technology-blog/index.php/2008/06/25/subsonic-select-where-in-solution. If that sounds confusing, imagine that you would like to have SubSonic return data that you would usually get with the following T-SQL statement:

select *
from MyTable 
where id in ('val1', 'val2', 'val3')

The author of the blog post had a solution but it was not one I liked. It seemed like a hack and I hate hacks. So, I took a few minutes and here is the result. The key is using the new SubSonic.Select query available in SubSonic v2.1. The code offers 3 different ways to accomplish the task.
C# Code:

// Define your collection object
MyTableCollection MyTableQuery;

// Define an array list for the first example
ArrayList ids = new ArrayList();
// Add some Guid values to the array list
ids.Add(new Guid("80FB189C-C3EA-4774-B15E-018DA88D3FE2"));
ids.Add(new Guid("88DE9C1D-26A7-4545-AA1E-01BDAEFF0588"));
    
// Poplulate the collection using an array list
MyTableQuery = new SubSonic.Select()
 .From(MyTable.Schema)
 .Where(MyTable.Columns.Id)
 .In(ids)
 .ExecuteAsCollection();

// Poplulate the collection using an array of strings
MyTableQuery = new SubSonic.Select()
 .From(MyTable.Schema)
 .Where(MyTable.Columns.Id)
 .In(new string[] {
  "80FB189C-C3EA-4774-B15E-018DA88D3FE2",
  "88DE9C1D-26A7-4545-AA1E-01BDAEFF0588"})
 .ExecuteAsCollection();
    
// Poplulate the collection using an inline select statement
MyTableQuery = new SubSonic.Select()
 .From(MyTable.Schema)
 .Where(MyTable.Columns.Id)
 .In(new SubSonic.Select(MyTable.Columns.Id)
 .Top("3")
 .From(MyTable.Schema))
 .ExecuteAsCollection();

VB.NET Code:

' Define your collection object
Dim MyTableQuery As MyTableCollection

' Define an array list for the first example
Dim ids As New ArrayList

' Define an array list for the first example
ids.Add(New Guid("80FB189C-C3EA-4774-B15E-018DA88D3FE2"))
ids.Add(New Guid("88DE9C1D-26A7-4545-AA1E-01BDAEFF0588"))

' Poplulate the collection using an array list
MyTableQuery = New SubSonic.Select() _
    .From(MyTable.Schema) _
    .Where(MyTable.Columns.Id) _
    .In(ids).ExecuteAsCollection(Of MyTableCollection)()

' Poplulate the collection using an array of strings
MyTableQuery = New SubSonic.Select() _
    .From(MyTable.Schema) _
    .Where(MyTable.Columns.Id) _
    .In(New String() {"80FB189C-C3EA-4774-B15E-018DA88D3FE2", "88DE9C1D-26A7-4545-AA1E-01BDAEFF0588"}) _
    .ExecuteAsCollection(Of MyTableCollection)()

' Poplulate the collection using an inline select statement
MyTableQuery = New SubSonic.Select() _
    .From(MyTable.Schema) _
    .Where(MyTable.Columns.Id) _
    .In(New SubSonic.Select(MyTable.Columns.Id) _
    .Top("3") _
    .From(MyTable.Schema)) _
    .ExecuteAsCollection(Of MyTableCollection)()