February 4, 2011 at 9:30 am
I have been asked to locate all occurrences of data manipulation within our database.
What?
OK an example
CREATE PROCEDURE p1
AS
SELECT datediff(year,[BIRTHDATE], getdate()) as [age]
from
I need to locate where the data presented to the client is not held within the database, but is calculated at the time of dilevery.
String manipulation is not my strong point and I do not have time to sit down and read every proc / view / function.
my prayers are for an undocumented system table holding this information have as yet gone unanswered.
I really need pointing in the right direction.
Many thanks in advance
Ian
February 4, 2011 at 9:39 am
Don't know if this helps - I use this to search for references in stored procs:
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%search%'
ORDER BY SO.Name
Leave out the line "AND SO.Type = 'P'" to include views, functions etc.
February 7, 2011 at 3:03 am
Thanks David,
but knowing what to search for is difficult.
Best I have so far is a list of column names that are in tables, the check each column name of views against these name, when not match I investigate manually. But locating the output of procs is prooving to be a lot harder to do programically.
February 7, 2011 at 3:29 am
So you want every single procedure that does any form of manipulation on the columns before returning it to the client. So concatenating strings, portions of strings, any form of mathematics (+, -, /, *, %), any date calculations, any case statements, etc? Anything other than SELECT col1, col2, col3 FROM tbl1?
If that is the case, writing a query search will probably take longer than reading each proc manually, and would be a lot more prone to error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 3:33 am
davidc-702140 (2/4/2011)
SELECT Distinct SO.NameFROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%search%'
ORDER BY SO.Name
sysobjects and syscomments are deprecated, included only for backward compatibility with SQL 2000 and should not be used for new development. They will be removed from a future version of the product.
Use sys.objects (or sys.procedures) and sys.sql_modules instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2011 at 3:45 am
Thanks Gail.
I was rapidly coming to that conclusion.
But it is nice to hear it confirmed, so I can proceed with my reading.
February 7, 2011 at 9:29 am
davidc-702140 (2/4/2011)
Don't know if this helps - I use this to search for references in stored procs:SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%search%'
ORDER BY SO.Name
Leave out the line "AND SO.Type = 'P'" to include views, functions etc.
and my version
DECLARE @find NVARCHAR(4000)
SELECT @find=''
;
WITH definitions as
(SELECT so.name,OBJECT_DEFINITION(object_id) AS text FROM sys.all_objects so)
SELECT text ,
name,
SUBSTRING(text,CHARINDEX(@find, text COLLATE Latin1_General_CI_AI)-40, 100) AS context
FROM definitions
WHERE
CHARINDEX(@find, text COLLATE Latin1_General_CI_AI)<>0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply