Friday, 27 February 2009

Finding start of week in T-SQL

Wah! So, the first two posts turn out to be HTML posts! Well, probably because I'm not doing much actual dot net programming right now :). Besides, this was too neat not to document. It's a line of SQL commands which will determine the first day of the current week. Could be extended to find the first day of any given week.

declare @weekStart datetime
select @weekStart = dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())
select @weekstart

You can also put some more jiggery-pokery round it in order to get midnight on the first day of the week:

declare @weekStart datetime, @now datetime
select @weekStart = dateadd(dd, 0, datediff(dd, 0, dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())))
select @weekstart

Thursday, 26 February 2009

Search Stored Procedures in T-SQL

So, I started a blog called "dot net notes" and my first post is about SQL. Doesn't look good but in honesty this is one of the most useful things I ever learned in SQL - how to do a blanket search of the text in all the stored procedures in a given database.

SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%text_to_search_for%'

Just replace "text_to_search_for" with, well, whatever you want to search for and you're good to go. Immensely helpful for tracking down chains of stored procedures which call each other, or examples of something you've done before in SQL but can't quite remember, or finding all the SP's in a database which reference a particular table or field. With enough tweaking it'd probably make you tea in the morning and wipe your arse for you.

Wednesday, 25 February 2009


OK, well what's this for? I program for a living - mainly asp dot net, but also SQL and classic asp. For a while now I've been collecting notes on how to do certain things - some neat solutions to problems I've found, some things I come up against time and time again but can never remember how to do, some common "gotchas" I've come up against.

And I figured I'd move it into a blog.

Why? Well, partly because it's easier for me to index and find things in a blog format. Partly because it's stuff other people might use. So if you find something useful on here, just enjoy. No need to comment unless I've made a fundamental error :)