Search

Tuesday, July 22, 2008

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

// //]]>