Link to home
Start Free TrialLog in
Avatar of skull52
skull52Flag for United States of America

asked on

Finding Where Clause Value in SQL Views and SP

I had to move several Views and Stored Procedures from one database to another, I need a way to find which views and SP contain a specific value in the where clause (i.e Where LOCCODE='CENTRAL')
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

On way is to expand the database in question and then open "Program ability" folder under it. Here you will see a list of all Stored Procedures and Views. Right click on them one by one and select Modify. This will display the code in the selected item where you can locate and visually verify what you are looking for.
Avatar of UnifiedIS
UnifiedIS

You can generate scripts of the views and procs (copy to all of them to one file) from SSMS and then search for that text.  If you have a very large amount, you might want to look for a third party tool that can generate scripts for multiple objects all at once.  Or, if you know .net, you can using microsoft.sqlserver.management.sdk.sfc namespace to write your own.
Avatar of skull52

ASKER

Mike, that is what I don't want to do, there are to many of them to make that a viable option.
Avatar of skull52

ASKER

Unified, Can you recommend a tool to accomplish this? The issue is that I don't know all the objects that contain that variable in the WHERE clause, that's what i am trying to find out.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

Open in new window


User generated image
Here, I have a proce named dbo.test1. I am attempting to identify the proc that contains "From dbo.employees". As shown in this image,  the code successfully finds the proc name test1.

Mike
Sorry, no recommendation. I wrote my own tool with .net. It uses the information_schema to return the objects from the database, generate create/alter scripts and then I can search them.  
Mike's script should give you the procs, just modify the where clause for your specific need. I don't know of a similar way to query view definitions. You might be able to look for the LOCCODE column  from INFORMATION_SCHEMA.COLUMNS to limit the list to manually review.  Or maybe by the table(s) where LOCCODE column is located.
with view, try:
SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%Where LOCCODE=%'

Open in new window



Then try: (after adding ' to skip the single quotes.)
SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%Where LOCCODE=''CENTRAL''%'

Open in new window

Avatar of skull52

ASKER

Mike,
None of the code returns any value
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Please give us an example of the code for one View and one SP that has the clause that you're looking for.
Additionally, for testing purpose, give us the scripts of one View and one Proc so we can see in fact what you are looking for exist in them. I know you will not do this in every case. This we need to make sure what we are looking exist in them to debug the solution if necessary.

The fact you are saying "None of the code returns any value" means that there is no View and/or Proc containing the string you are looking for that is if the coding is correct which we will verify when we see your code requested by Vitor.

Thanks,

Mike
Avatar of skull52

ASKER

Below is a view that has CENTRAL in the WHERE Clause and running your scripts  displays no results. I am on SQLSERVER 2008R2

CREATE VIEW [dbo].[SSGPODAYSTOPRM]
AS
SELECT     dbo.POP10110.LOCNCODE, dbo.POP10110.ITEMNMBR, CASE WHEN pop10110.POTYPE = 1 THEN ((POP10110.QTYORDER - POP10110.QTYCANCE) 
                      * POP10110.UMQTYINB - CASE WHEN QTYSHPNET IS NULL THEN 0 ELSE QTYSHPNET END) ELSE 0 END AS QTYREMAIN, 
                      dbo.POP10110.PONUMBER, dbo.POP10110.ORD, CASE WHEN dbo.POP10110.PRMDATE > dbo.POP10100.PRMDATE THEN (dbo.POP10110.PRMDATE) 
                      ELSE (dbo.POP10100.PRMDATE) END AS PRMDATE, CASE WHEN dbo.POP10110.PRMDATE > dbo.POP10100.PRMDATE THEN (DATEDIFF(dd, 
                      GETDATE(), dbo.POP10110.PRMDATE)) ELSE (DATEDIFF(dd, GETDATE(), dbo.POP10100.PRMDATE)) END AS DAYSTOPRM, 
                      CASE WHEN pop10110.POTYPE = 1 THEN ((POP10110.QTYORDER - POP10110.QTYCANCE) 
                      * POP10110.UMQTYINB - CASE WHEN QTYSHPNET IS NULL THEN 0 ELSE QTYSHPNET END) ELSE 0 END * dbo.POP10110.UNITCOST AS TCOST
FROM         dbo.POP10110 INNER JOIN
                      dbo.POP10100 ON dbo.POP10110.PONUMBER = dbo.POP10100.PONUMBER LEFT OUTER JOIN
                      dbo.SSGPOSHIPNET ON dbo.POP10110.PONUMBER = dbo.SSGPOSHIPNET.PONUMBER AND 
                      dbo.POP10110.ORD = dbo.SSGPOSHIPNET.POLNENUM
WHERE     (dbo.POP10110.POLNESTA > 1) AND (dbo.POP10110.POLNESTA < 4) AND (dbo.POP10110.RELEASE = 1) AND (dbo.POP10110.LineNumber <> 0) AND 
                      (dbo.POP10110.LOCNCODE = 'CENTRAL')

GO

Open in new window

Avatar of skull52

ASKER

I just got it to work with

SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%CENTRAL%'

Open in new window

Looking only for 'CENTRAL' may return non wanted records. I would suggest to tight more the search criteria, like for example adding the single quotes so you can avoid tables or column names that has CENTRAL  on their name:
SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%''CENTRAL''%'

Open in new window

Also try:
SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%WHERE%'

Open in new window


Just to make sure the code works. I know you are not looking for string WHERE but we are doing this to make sure if there is some other reason why it is not getting the expected result.

Mike
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skull52

ASKER

Once I removed '%Where LOCCODE= from your code it worked just fine. I think it was looking for that exact string
Avatar of skull52

ASKER

Thanks for the help
Hi skull52,

Vitor at ID: 42096989 had a good input by including:

WHERE  VIEW_DEFINITION like '%''CENTRAL''%'

Which is a part of solution you have used. I thing, he needs to get assist points for that. After an okay from you, I think he can as an EE administrator can handle the rest.

Regards,

Mike
Avatar of skull52

ASKER

Yes I agree, did not mean to slight his contribution. The only reason I added my solution is that is the one that worked with the combined input from you and Vitor.
Thank you skull52. BTW, you can have more than on assist solution.

Mike