Regular Expressions in SQL Server Management Studio (SSMS) - General SQL Database Question & Answer

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » General SQL Database Question & Answer » Regular Expressions in SQL Server Management Studio (SSMS)

Regular Expressions in SQL Server Management Studio (SSMS)

Options

Previous Topic · Next Topic

Scott Whigham

Posted: Monday, October 09, 2006 9:31:21 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 329
Points: 700
Location: Dallas, TX

I just totally love the regular expression find/replace tool in SSMS. I thought I would dump out some of my favs that I use here so that, when I need them again, I'll have a single place to refer back to them.

^set \@p(:z+)=.*\n -- finds anything like SET @p1=NULL or SET @P12='hello World' with a carriage return at the end. This is particularly helpful when you are using SQL Profiler to trace calls to stored procedures that have OUTPUT parameters. When you trace SQL Server stored procs with OUT params, SQL Profiler will show you something like this:

Code:

declare @p3 nvarchar(2048)
set @p3=505
declare @p4 nvarchar(256)
set @p4='Whatever'
declare @p5 nvarchar(256)
set @p5='Junk data'
declare @p6 nvarchar(128)
set @p6='Junk data'
exec dbo.GetMyDetailItem @MyId=N'MyId',@ProductId=505,@Description=@p3 output,@DescrKeys=@p4 output,@DescrDescription=@p5 output,@Title=@p6
output
select @p3, @p4, @p5, @p6

This code is not too clean to me and I generally will do at least one Find/Replace (^set \@p(:z+)=.*\n) which yields

Code:

declare @p3 nvarchar(2048)
declare @p4 nvarchar(256)
declare @p5 nvarchar(256)
declare @p6 nvarchar(128)
exec dbo.GetMyDetailItem @MyId=N'MyId',@ProductId=505,@Description=@p3 output,@DescrKeys=@p4 output,@DescrDescription=@p5 output,@Title=@p6
output
select @p3, @p4, @p5, @p6

Much cleaner!

My next find/replace generally is to get rid of all the "extra" DECLARE's and be left with an easier to read doc:

Code:

declare @p3 nvarchar(2048), @p4 nvarchar(256), @p5 nvarchar(256), @p6 nvarchar(128)
exec dbo.GetMyDetailItem @MyId=N'MyId',@ProductId=505,@Description=@p3 output,@DescrKeys=@p4 output,@DescrDescription=@p5 output,@Title=@p6
output
select @p3, @p4, @p5, @p6

Don't forget to click the box to use Regular Expressions

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.