March 24, 2011 at 2:36 am
I used the term cursor incorrectly. I come from FoxPro where cursors are similar to temporary tables. I get two grid results when I run the query in MSSMS
This is the whole code. I use it a lot to find variables in procs and tables.
Unfortunately, I still don't know how to get the repeating values out.
March 24, 2011 at 11:38 am
This free tool shows all matches in context...
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
March 26, 2011 at 11:33 am
Craig Farrell (3/23/2011)
Jeff Moden (3/22/2011)
FredS-1001785 (3/21/2011)
The original problem I was trying to solve was that I wanted to make sure that I was dropping temporary tables after using them. Hence, searching for '#' should give me all the procedures that have a temporary table, and if I don't see a drop table in the procedure I need to go look at it.Why are you dropping temporary tables? They drop automatically after a session comes to an end. Are you pooling connections or something similar?
It doesn't hurt anything to explicitly do it in your procs and I do the same thing to make my life easier during testing when I'm running and re-running the proc in the same window without the proc wrapper. It's mostly for convenience later when something breaks and I need to test the guts of the proc.
I'm not 100% sure but it seems to me that I remember a post somewhere where it said that it does matter especially for GUI related code that gets hit a lot. IIRC, since the advent of 2K8, the "structure" of the Temp table is cached if the table isn't explicity dropped.
For non-GUI related batch SQL, I'm with you 100% though I'll contitionally drop the Temp tables at the beginning of the sproc instead of at the end to make troubleshooting easier.
--Jeff Moden
March 26, 2011 at 4:40 pm
Jeff Moden (3/26/2011)
I'm not 100% sure but it seems to me that I remember a post somewhere where it said that it does matter especially for GUI related code that gets hit a lot. IIRC, since the advent of 2K8, the "structure" of the Temp table is cached if the table isn't explicity dropped.For non-GUI related batch SQL, I'm with you 100% though I'll contitionally drop the Temp tables at the beginning of the sproc instead of at the end to make troubleshooting easier.
Hmmm, yet another thing to learn on the new version for me. Yeah, I do the same, and usually leave a set of DECLARE/SELECT for the parameters in a comments field as well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 27, 2011 at 3:29 am
I got it to work, but I'm not real happy with it.
IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
GO
use Your_DB
DECLARE @StringToSearch VARCHAR(100), @ProcSearch VARCHAR(102), @Tot int
-- Change this string to change the search ---------------------------------------------------------
SET @StringToSearch = '#'
----------------------------------------------------------------------------------------------------
SET @ProcSearch = '%' +@StringToSearch + '%'
;with cte as (SELECT distinct SO.Name, SO.Type
FROM sysobjects as SO
INNER JOIN syscomments as SC
on SO.Id = SC.ID
AND SC.Text LIKE @ProcSearch)
select SO.Name, SO.Type, SO.id, SC.text,
ROW_NUMBER() over(order by SO.Type, SO.Name) as RN
into #T1
FROM sysobjects as SO
INNER JOIN syscomments as SC
on SO.Id = SC.ID
inner join cte as SL
on SO.name = SL.name
and SO.type = SL.type
ORDER BY SO.Type, SO.Name
set @Tot = @@ROWCOUNT
--select * from #T1
declare @out table (Name varchar(50),
Type char(5),
Usage varchar(100))
declare @finds int = 0
,@next int = 0
,@offset int = 0
,@RN int = 1
while @RN < @Tot
begin
select @next = 0, @offset = 1, @finds = 0
while @finds < 6
begin
select @offset = patindex(@ProcSearch, SUBSTRING(text, @next + 1, len(text)))
from #T1 where RN = @RN
if @offset = 0 break
select @next = @next + @offset, @finds = @finds + 1
insert into @out (Name,Type,Usage)
select Name, TYPE,
fndText = case
when @next < 12
then left(text, 100)
else substring(text, @next - 12, 100) end
from #T1 where RN = @RN
end
set @RN = @RN + 1
end
select * from @out
SELECT o.name as [Table]
,c.name as [Column]
FROM sys.tables o
inner JOIN sys.columns c
ON o.object_id = c.object_id
WHERE o.type = 'U'
AND c.name = @StringToSearch
order by c.name
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply