November 29, 2012 at 9:49 am
Hello All,
I have 3 databases ABC, DEF, GHI. I need to identify all the database objects (Stored Procedures, functions,views etc) which are using a specific column called RegulatoryTypes only in the filter (where condition). But there are multiple tables with the column RegulatoryTypes but with different names like table 1 has LineRegulatoryType, Table 2 has RegulatoryTypes, Table 3 has RegulatoryType but whatever the column name the string RegulatoryType is consistent for all the tables which has this column. I am trying this but not working properly.
USE DERIVEDASSET
GO
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%'+'Where'+' '+'%Regulatory%'+'%'
AND TYPE = 'P'
order by SPName
GO
Please help me on how to achieve this.
Thanks
November 29, 2012 at 9:59 am
You might want to take a look at RedGate SQL Search.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 10:10 am
Actually I need a T-SQL Script for this. Please help me if you have any idea.
November 29, 2012 at 10:17 am
I am trying this but not working properly.
What you have looks like it should work. Not sure what you mean by not working. You did mention that you want to find only where clauses in procs, views and functions. Your script will only look in procs because you limit the type to 'P'.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 11:53 am
I am trying to do with the above query whatever I have inserted in the block and it is partly working. But it is also getting the objects which are just referencing that column and I want those to get in the result set. I want only the objects which are using the column in the where clause.
November 29, 2012 at 12:13 pm
It will most likely take some manual manipulation to get only those that have that column in the where clause. Keep in mind that you are dealing with varchar data. It would take a monumental amount of effort to come up with all the permutations that could be there. You are going to have to use something like that query to get a reasonable list and then look at the code with your eyes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 12:27 pm
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ' USE ?; SELECT ''?'' AS DATABASE_NAME,
sys.schemas.name AS [Schema Name], sys.objects.name AS [Object Name] ,
type_desc AS [Object Type]
FROM sys.objects JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE (CHARINDEX(''' + 'Where' + ''',definition)<(CHARINDEX(''' + 'RegulatoryType' + ''',definition)))'
EXEC sp_MSforeachdb @SQL
I am also trying like this. But no luck.
November 29, 2012 at 12:31 pm
srik.kotte (11/29/2012)
DECLARE @SQL VARCHAR(MAX)SET @SQL = ' USE ?; SELECT ''?'' AS DATABASE_NAME,
sys.schemas.name AS [Schema Name], sys.objects.name AS [Object Name] ,
type_desc AS [Object Type]
FROM sys.objects JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE (CHARINDEX(''' + 'Where' + ''',definition)<(CHARINDEX(''' + 'RegulatoryType' + ''',definition)))'
EXEC sp_MSforeachdb @SQL
I am also trying like this. But no luck.
What does no luck mean? Does it error? Does it not return what you expect?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 12:35 pm
Yes it is not returning what I expect. I want resultset of only the objects which are using RegulatoryType column in the where clause. But I am getting the objects in the resultset which are just referencing that column along with the objects which are using this column in the where clause. All I want is the objects which are using the column in the where clause. I dont want to see the objects which are referncing that column but not used in the where clause.
November 29, 2012 at 12:37 pm
Of course even the thing you just posted can't possibly take into account complex things. What about a cte in a proc that has a where statement and the value 'Regulatory' is somewhere else in the code? What about subqueries? There are just way too many variable to isolate this pro grammatically. Why does the script have to be perfect? This seems like something for a one time fix.
Take a look at this obvious made up mess below:
create proc CrazyNameTest
as begin
;with cte as
(
Select 1 as Col1 where 1 = 1
)
select 'Regulatory' from cte
end
This would meet your criteria of having a where clause and the word "Regulatory" somewhere later in the string. The point I am making is what you are trying to do is impossible to be 100% accurate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 12:43 pm
Sean Lange (11/29/2012)
This would meet your criteria of having a where clause and the word "Regulatory" somewhere later in the string. The point I am making is what you are trying to do is impossible to be 100% accurate.
I'm forced to second this evaluation of your requirements. There is nothing that determines if a column is in a where clause for a statement other than the straight text evaluation of the procedures themselves. With multi-statement procedures and functions, you cannot trust that what is after a WHERE statement is actually part of the main query.
You'll have to do some filtering work yourself, unfortunately, after you find all the objects that reference the field(s).
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
November 29, 2012 at 1:35 pm
Thnaks for your reply. But I didn't get what that stored proc is doing and how it gonna fix my issue. Please explain.
November 29, 2012 at 1:38 pm
srik.kotte (11/29/2012)
Thnaks for your reply. But I didn't get what that stored proc is doing and how it gonna fix my issue. Please explain.
It won't fix your issue. It was an example of a stored proc that has more than a single select statement. You have to realize that you are searching strings. The string generated by that proc I posted is an example of how impossible the search is you are trying to accomplish. You would be writing code for the next hundred years to make a script that will find exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2012 at 4:12 am
I need to identify all the database objects (Stored Procedures, functions,views etc) which are using a specific column called RegulatoryTypes only in the filter (where condition).
In short - It is impossible with the current level of A.I. (artificial intelligence) in SQL.
Strategy would be:
1. Find all objects which refer to this column at any place of their bodies.
2. Manually review every such object.
There are no other options.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply