skull52
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')
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.
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.manage ment.sdk.s fc namespace to write your own.
ASKER
Mike, that is what I don't want to do, there are to many of them to make that a viable option.
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'
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.
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
with view, try:
Then try: (after adding ' to skip the single quotes.)
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%Where LOCCODE=%'
Then try: (after adding ' to skip the single quotes.)
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%Where LOCCODE=''CENTRAL''%'
ASKER
Mike,
None of the code returns any value
None of the code returns any value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
I just got it to work with
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%CENTRAL%'
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''%'
Also try:
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
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%WHERE%'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Once I removed '%Where LOCCODE= from your code it worked just fine. I think it was looking for that exact string
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
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
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
Mike