Wednesday, May 24, 2006

Intellisense for SQL Server

I dunno if you've heard of Red-Gate. but so far they've created some very top notch software to help out with both webservices and Sql Server. I've personally tried both their Sql Data Compare and Sql Compare wich helps match both schema and data accross diffrent databases and servers. This has many benefits as you can easily work in a test environment and have the ability to work with some close to live data. and after you've completed your work you can propagate the changes to your test environment with out a hassle not to mention you can minimize the time it takes to roll changes to your live environment.

But really what I wanted to talk about today was Sql Prompt. It's Intellisense for Sql Server. That's it. Well that's not just it. It loads a systray app in your system and hooks directly to your Query Analyzer. Off the cuff I found it to be extreamly effective. I started to write my Select statement and it was able to appropriately give me back names of tables to complete and even name of stored procedures that were available to me. One of the options that I liked the best, was the join feature, in which it helps you complete a join statement super quick by giving you a small list of all available joinable fields. you can type at your desire or pick from the list... ultra cool.
Well now for the hook and sinker... It's Free :). Unlike the other products that help make them money. Probably the most useful app they have on their site is completely free. Of course after using this little proggy you're gonna probably want to return and see what other cool apps they have. To get your copy click on the following link:
SQL Intellisense, Intellisense for SQL Server - SQL Prompt

After using it for a few minutes I was able to get the hang of it. Extra niceties are things such as auto-completion for your parameter names for a sproc (stored procedure). nice touch guys!

btw, nope I do not work for Red-Gate, I just happend to have really liked a few of their Software Titles. :)

---*update*---
SQLprompt will only be free until Sept 1st.

Monday, May 01, 2006

Find and Replace

I cruised the net last week looking for a simle "Find and Replace" script. sorta like a split for VB only that I need the replace to occur at every nth character. Not finding what I wanted I ended up writing my own, the idea was that I'd start at the lenght that my string needs to be then crawl backwards until I find a space (or breakpoint) then replace it with my Replace string. There may be a better way.. if you have one feel free to comment.


CREATE FUNCTION dbo.SplitAndReplace
(@String AS VARCHAR(8000), @SplitString CHAR(1), @ReplaceString VARCHAR(255), @BreakInterval AS INT
)
RETURNS VARCHAR(8000)

AS

BEGIN
/*
SET @String = 'This is as very simple text that needs to be broken down for processing in the external system, the process should auto insert an exclamation-pound sign in lew of a space that is at 60 characters'
SET @BreakInterval = 60
SET @ReplaceString = '!#'
SET @SplitString = ' '
*/
DECLARE @X AS INT
DECLARE @Y AS INT
SELECT @X = 0, @Y = 0


WHILE (LEN(RTRIM(LTRIM(@String))) - @Y) / @BreakInterval > 0
BEGIN

WHILE @SplitString <> SUBSTRING(@String, @Y + (@BreakInterval - @X), 1)
BEGIN
SET @X = @X + 1
END


SET @String = STUFF(@String, @Y + (@BreakInterval - (@X )) , 1, @ReplaceString)
SET @Y = @Y + (@BreakInterval - @X)
END
RETURN @String
END