August 29, 2011 at 4:57 am
I have a SQL 2000 in-house website reservation system and planning on migrating to SQL 2008 but there is a time zone issue. I am looking for a tool or suggestion to find all the objects that use getdate() or getutcdate() and replace it with the correct time zone different from the database server timestamp. Does anybody have any recommendation?
August 30, 2011 at 5:42 am
it's pretty easy to search through the text of procedures and functions in sql server, but it's possibly safer to search through the source code repository,often it has defined searches.
I'm sure there are some routines on the site to search code inside sql server. you use syscomments in sql 2000. There's a possibility I seem to remember that will not find a string if the source code crosses a 8kb page boundary in syscomments. here's a proc I used in sql 2000, it'll work in 2005 and 2008 too. It doesn't resolve the 8kb page issue though.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 08/30/2011 12:39:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_FindText]
@wot varchar(250)=' ',
@wot2 varchar(250)=' '
-- ============================================================================
-- Stored Procedure: sp_FindText
--
-- Written by: Colin Leversuch-Roberts
--kelem consulting limited
-- http://www.kelemconsulting.co.uk
--(c) august 2004
--
-- Purpose: Search for system objects containing the passed string(s)
--These are wild card searches
--Returns the object code and type of object
--
-- System:master database
--does not need to be marked as a system object
--
-- Input Paramters: @wotvarcharSearch string
-- @wot2varcharSearch string
--
-- Output Parameters: None
--
-- Usage: Call from user database to be searched
-- EXEC dbo.sp_findtext 'auganiser'
--EXEC dbo.sp_findtext 'aug','uat'
--
-- Calls: nothing
-- Uses:syscomments, sysobjects
--
-- Data Modifications: None
--
-- VERSION HISTORY
-- Version NoDateDescription
-- 122-August-2004Initial Release
-- ============================================================================
as
set nocount on
--
select obj.name,obj.xtype,
case obj.xtype
when 'TR' then 'Trigger'
when 'P' then 'Procedure'
when 'V' then 'View'
when 'TF' then 'Function'
when 'IF' then 'Function'
when 'FN' then 'Function'
else 'Unknown'
end
,c.text
from dbo.syscomments c with (nolock) join dbo.sysobjects obj with (nolock)
on obj.id=c.id
where
patindex('%'+@wot+'%',text)<>0 and patindex('%'+@wot2+'%',text)<>0
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 31, 2011 at 5:16 am
Great, thank you. This truly identifies the objects that I am looking for. My other question is what is the best way of changing getdate() from pacific to central time zone?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply