March 18, 2011 at 3:46 am
I use the following code to find references to variables.
SET @StringToSearch = 'drop table'
SET @ProcSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name, SO.Type
,patindex(@ProcSearch, SC.text) as location
,fndText = case
when patindex(@ProcSearch, SC.text) < 10
then left(SC.text, 100)
else substring(SC.text, patindex(@ProcSearch, SC.text) - 10, 100) end
FROM sysobjects as SO (NOLOCK)
INNER JOIN syscomments as SC (NOLOCK) on SO.Id = SC.ID
AND SC.Text LIKE @ProcSearch
ORDER BY SO.Type, SO.Name
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
This gives me references to a given variable and works to a point. However, it doesn't give me all the references.
Thanks
March 18, 2011 at 5:28 am
how do you know it doesn't? It want way doesn't it work? (I know it won't for encrypted procs, but other than that it looks ok).
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 18, 2011 at 5:40 am
When I search for '#' in the following code it only finds the first reference.
CREATE PROCEDURE [dbo].[AREA_CustomerIssues3]
@ParType char(2) = ''
,@ParKey int = null
AS
BEGIN
SET NOCOUNT ON;
--select @ParType, @ParKey
;with cte as(SELECT A.Cust_key, A.Mast_key --as ParKey
,A.AreaName, A.area_key as ParAreaKey
FROM (SELECT AreaName, max(Area_key) as maxKey
from dbo.commArea2Manage as A
where (@ParType = 'CU' and (Cust_key = @ParKey or
@ParKey is null and Cust_key is not null))
or (@ParType = 'MC' and (Mast_key = @ParKey or
@ParKey is null and Mast_key is not null))
group by mast_key, cust_key, AreaName) as PAreas
inner join dbo.commArea2Manage as A
on PAreas.maxKey = A.Area_Key)
--select * from cte
SELECT
-- Master Customers
M.mastcust_code as ParentName
,C.cust_name as ChildName
,D.AreaName -- as PossibleArea
,P.ParAreaKey
,CAreas.maxKey as ChildAreaKey
,C.mast_key as ParKey
,C.Cust_Key as ChildKey
into #TT1
-- Establish the list of Customers and possible areas
from psc_comp.dbo.commCustomers as C
inner join defValidAreaName as D
on D.UseInAreaManager = 1 -- Just all possible areas
and @ParType = 'MC'
and (C.Mast_key = @ParKey or @ParKey is null)
inner join dbo.commMaster_Cust as M
on C.mast_key = M.mast_key
and C.cust_end is null
left outer join cte as P
on P.Mast_key = C.mast_key
and P.AreaName = D.AreaName
left outer join (SELECT AreaName, A.Cust_key,max(Area_key) as maxKey
from dbo.commArea2Manage as A
inner join psc_comp.dbo.commCustomers as CC
on CC.cust_key = A.Cust_key
group by A.Mast_key, A.Cust_key, AreaName) as CAreas
on C.cust_key = CAreas.Cust_key
and D.AreaName = CAreas.AreaName
union
-- Customers
SELECT C.Cust_Name as ParentName
,S.s_code as ChildName
,D.AreaName -- as PossibleArea
,P.ParAreaKey
,CAreas.maxKey as ChildAreaKey
,S.cust_key as ParKey
,S.Site_Key as ChildKey
from psc_comp.dbo.commSites as S
inner join defValidAreaName as D
on D.UseInAreaManager = 1 -- Give me all possible areas
and (S.Cust_key = @ParKey or @ParKey is null)
and @ParType = 'CU'
inner join psc_comp.dbo.commCustomers as C
on S.cust_key = C.cust_key
and S.site_end is null
left outer join cte as P
on P.Cust_key = S.Cust_key
and P.AreaName = D.AreaName
left outer join (SELECT AreaName, A.Site_key, max(Area_key) as maxKey
from dbo.commArea2Manage as A
inner join psc_comp.dbo.commSites as SS
on SS.site_key = A.Site_key
group by A.Site_key, AreaName) as CAreas
on S.site_key = CAreas.Site_key
and D.AreaName = CAreas.AreaName
--select * from #T1
select * from #TT1
where (ParAreaKey is not null
or ChildAreaKey is not null)
order by ParentName, ChildName, AreaName
select distinct A.*
from dbo.commArea2Manage as A
inner join #TT1 as T
on A.Area_key = T.ParAreaKey
or A.Area_key = T.ChildAreaKey
drop table #TT1
END
March 18, 2011 at 6:01 am
Not to be rude, but are you just searching for #? It would only return one if you ended up searching for 'drop table #'
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 18, 2011 at 6:38 am
No, I am searching for the one character long string '#'
March 20, 2011 at 5:08 am
because you are traversing each SP just once, once the serchstring found, code is skipping all other refernces in the same SP.
March 20, 2011 at 5:49 am
Agreed. I don't know how to get the repeating values out of the search string.
March 20, 2011 at 6:02 am
cursors can surley solve this, I am not sure about any other solution
March 20, 2011 at 10:00 am
Instead of writing your own search utility, go to Redgates site and download their free SQL Search utility. This will search all objects in the database - or system, for the string you want.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 20, 2011 at 1:24 pm
FredS-1001785 (3/18/2011)
No, I am searching for the one character long string '#'
What do you want to do when you find the character more than once? Are you simply looking for a count or are you looking for some number of characters after the #?
--Jeff Moden
March 20, 2011 at 2:31 pm
Okay, waaaait a second here, I'm confusioned.
I'm assuming your concern is that if you have 10 DROP TABLE statements in a procedure, you'd like to see ten of the 100 character instances of it's usage, and then repeat this by procedure?
The PATINDEX is locating only the first piece, and you have no loops in place. Thus, correct, you'll only see the first component. You need to have some kind of looping in place to locate all instances. My recommendation here would be to take one proc of this type and test speed of a tally table vs. a while loop. If you need more information on the Tally table, check my signature for the link to Jeff Moden's article.
You're going to have to have some way of looping the proc, and skipping the first instance to find the second, skipping to after the second to find the third... etc.
As a second question... are you ever expecting a proc to have the name 'DROP TABLE', or is that supposed to be a wildcarded like?
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 21, 2011 at 3:05 am
I use this as a maintenance/development tool so speed is not important. @StringToSearch is changed each time I use it.
For instance, I want to find every place I used the variable 'LegalName'. I would run the following:
declare @StringToSearch varchar(100), @ProcSearch varchar(100)
SET @StringToSearch = 'LegalName'
SET @ProcSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name, SO.Type
,patindex(@ProcSearch, SC.text) as location
,fndText = case
when patindex(@ProcSearch, SC.text) < 10
then left(SC.text, 100)
else substring(SC.text, patindex(@ProcSearch, SC.text) - 10, 100) end
FROM sysobjects as SO (NOLOCK)
INNER JOIN syscomments as SC (NOLOCK) on SO.Id = SC.ID
AND SC.Text LIKE @ProcSearch
ORDER BY SO.Type, SO.Name
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
I get two cursors. The first has references in code with a short string to show the usage. The second has the columns in the tables.
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.
March 22, 2011 at 8:22 pm
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?
--Jeff Moden
March 23, 2011 at 5:42 pm
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.
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 23, 2011 at 5:48 pm
FredS-1001785 (3/21/2011)
I get two cursors.
Well, no cursor declares in this code, I assume this is an excerpt then.
The first has references in code with a short string to show the usage. The second has the columns in the tables.
The first only finds the first reference, there is no looping past the first find, so you will only ever see one code snippet with this method per proc.
Are you trying to get the columns of the # table, or is this a generic proc you want to use to also find out when procs and tables are all using a column when you're interrogating a database?
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.
That would require a few queries, one for the #, one for the DROP, and a third to contrast the two.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply