July 23, 2013 at 5:59 am
Comments posted to this topic are about the item Find Stored Procedures and Functions That Reference Certain Tables
Hakim Ali
www.sqlzen.com
August 2, 2013 at 7:02 am
Hakim - this script is excellent. It will help me to locate "no longer needed" procedures in my development process. Thanks for posting!
August 2, 2013 at 7:34 am
This is nice, helpful and I hope to see more from you but the script take for granted that the searched object is unique amongst all other objects name even part of it.
For instance looking for Table1 when Table123 is solely used inside an object will shows that object as if Table1 used it instead which is not the case.
August 2, 2013 at 8:40 am
Hakim,
This is good.
Sometime in the past I read that you were supposed to use information_schema instead of the sys tables for this type of thing, and had been using them to find deprecated column references during upgrades. Compared my script with yours, and found that yours found references that I had missed. I never noticed before today that the corresponding definition fields in the information_schema views are limited to the first 4000 characters of the script.
August 2, 2013 at 8:47 am
arthur.teter (8/2/2013)
I never noticed before today that the corresponding definition fields in the information_schema views are limited to the first 4000 characters of the script.
Same with syscomments, which you should avoid using because it's only there for backward compatibility and could be deprecated in a future release. Not only that, sys.sql_modules has the whole definition on one row, so you won't miss out if your search string straddles the 4000-character boundary.
John
August 4, 2013 at 10:32 am
sdorris 90134 (8/2/2013)
Hakim - this script is excellent. It will help me to locate "no longer needed" procedures in my development process. Thanks for posting!
Thank you.
Hakim Ali
www.sqlzen.com
August 4, 2013 at 10:35 am
Megistal (8/2/2013)
This is nice, helpful and I hope to see more from you but the script take for granted that the searched object is unique amongst all other objects name even part of it.For instance looking for Table1 when Table123 is solely used inside an object will shows that object as if Table1 used it instead which is not the case.
Thanks for pointing that out. I hadn't considered this scenario, maybe because I haven't encountered a situation with objects named that way. I guess I could code for it by checking the charindex of, say Table1, and comparing it to the charindex of Table123. If they have the same value, I know I have Table123 and not table 1.
Hakim Ali
www.sqlzen.com
August 4, 2013 at 10:38 am
arthur.teter (8/2/2013)
...Sometime in the past I read that you were supposed to use information_schema instead of the sys tables for this type of thing...
I generally prefer using information_schema too, as that is the ansi standard and will generally work on all relational databases and is more resistant to being obsoleted. I make exceptions when using sys.* results in easier code. And not all database objects are available through information_schema, sometimes I have to use sys.*.
Hakim Ali
www.sqlzen.com
August 4, 2013 at 10:39 am
John Mitchell-245523 (8/2/2013)
...sys.sql_modules has the whole definition on one row...
Thanks for the tip, will check out sys.sql_modules.
Hakim Ali
www.sqlzen.com
April 10, 2015 at 5:40 am
<edit>
April 10, 2015 at 6:51 am
Thanks for the script.
There is also a point-and-click method by right-clicking a table in SSMS and selecting View Dependencies.
Enjoy!
April 10, 2015 at 7:28 am
Hakim:
Why do you need to join the system view syscomments twice? I think you only need to alias it once. Here is the modified code:
selectdistinct
ObjectType= o.type_desc
,ObjectName= o.name
,CodeSequence= c_display.colid
,Code= c_display.[text]
fromsys.objects o
/*
inner joinsys.syscomments c_search
on c_search.id = o.[object_id]
*/
inner joinsys.syscomments c_display
on c_display.id = o.[object_id]
whereo.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
/*
andc_search.[text] like '%search_term%'
*/
andc_display.[text] like '%search_term%'
order by1, 2, 3
April 10, 2015 at 7:41 am
Here is some code I borrowed from someone, it searches across all databases on the server.
DECLARE @SQL VARCHAR(8000)
DECLARE @SearchText NVARCHAR(255)
SET @SearchText = 'table_name_here'
DECLARE @Results TABLE
(
[SERVERNAME] VARCHAR(255),
[DBName] VARCHAR(255),
[NAME] VARCHAR(255),
XTYPE VARCHAR(255)
)
;
SELECT @SQL =
'SELECT DISTINCT @@SERVERNAME, ''?'' AS [DBName], so.[name], so.xtype
FROM [?].dbo.sysobjects so WITH(NOLOCK)
inner join [?].dbo.syscomments sc on
so.id = sc.id
INNER JOIN [?].sys.databases d ON
d.Name=''?''
AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
WHERE sc.text like ''%'+ @SearchText + '%'''
INSERT INTO @Results
EXEC sp_MSforeachdb @SQL
SELECT *
FROM @Results
ORDER BY DBName, XType, [Name]
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 10, 2015 at 2:07 pm
I'm late to the game on this and just skimmed the article, but I'm confused why you wouldn't just use Redgate's free sql search tool. That tool is so awesome and has saved me so much time.
I believe SQL Search runs this same query, but provides you a nice interface and allows you to quickly navigate to the item in object explorer. I work as a consultant and will do reporting on complex system. I think it should be at least mentioned so noob's out there will know about it as an alternative, because it is one of the most powerful tools in your toolbox.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply