Here you go, try this. You'll need a Tally table first (see here[/url]):
DECLARE @startDate datetime, @endDate DATETIME
SELECT @startDate = '20090101', -- First day in range
@endDate = '20090401' -- Day *after* last day
--== Note that my tally table starts at 1 hence the N-1 below
N <= DATEDIFF(dd,@startDate,@endDate) AND
--== Adding the day of the week to the @@DATEFIRST value then doing MOD 7
--== normalizes the result to be independent of the DATEFIRST setting
--== 6 just happens to be Friday
(DATEPART(dw,DATEADD(dd,n-1,@startDate)) + @@DATEFIRST) % 7 = 6
You could put it in a stored procedure with the start date, end date and day of week you're interested in as parameters.
It isn't all that different from using a calendar table. But you seemed concerned about the storage requirements. A Tally table would use less space.
There are other ways without using a tally table - common table expressions and ROW_NUMBER are just two (if you're using 2005 or above).