SQLServerCentral / Discuss content posted by Jano Petras / Article Discussions / Article Discussions by Author / When sp_depends fails / Latest Posts

RE: When sp_depends fails

Ninja's_RGR'us — Thu, 04 Sep 2008 09:54:55 GMT

One more thing to keep in mind, if the proc has more than 4000 characters and that the objects used is splitted between 2 rows in syscomments, that still won't work (quite rare, but it does happen).The solution is to left join on syscomments on c1.id = c2.id and c1.colid = c2.colid + 1then do the search on c1.text + isnull(c2.text,'') like '%Whatever%'That was true in 2000, maybe it's been fixed in 2005, but I'd be surprised.

RE: When sp_depends fails

bitbucket-25253 — Thu, 04 Sep 2008 08:47:47 GMT

Does not workSELECT * FROM information_schema.routines r WHERE charindex('Dbo.People2', r.ROUTINE_DEFINITION)>0Does workSELECT * FROM information_schema.routines r WHERE charindex('People2', r.ROUTINE_DEFINITION)>0Nate Schmidt [quote]fully qualified my tables ([database].[schema].[tablename], I suppose putting a period in the front would work...but I don't[/quote]Tried that and it does NOT workMy conclusion is it will not work with a fully qualified name, and/or the inclusion of the schema name.Suggest that a note accompany your script to that effect. Now knowing that ,it is an EXCELLENT script and will prove to be very useful... THANKS for contributing.

RE: When sp_depends fails

pheiner — Thu, 17 Jul 2008 11:41:56 GMT

Not trusting sp_depends or information_schema.routines when they get updated I took the idea of the first code and wrote a queries to scan syscomments Still need to clean it up but here is the first pass.--gets the list of the table.column that are used in a spselect o.name+'.'+sc.namefrom sys.objects ojoin sys.columns sc on sc.object_id = o.object_idjoin syscomments c on 1 = 1 --don't want to joinwhere charindex (' '+o.name , c.text)> 0 --not sure if the space is needed. and charindex (sc.name , c.text)> 0 and c.id = object_id('stored procedure name') and o.type = 'u' --might what this to be o.type in ('u','p')group by o.name, sc.nameorder by 1 ;FYI, sorry about not putting the [] on the object_id columns but it runs fine and who ever made the columns a reserved name should be Thanks for posting the original.

RE: When sp_depends fails

eric.bennett2 — Tue, 27 May 2008 13:18:56 GMT

An alternative I recently discovered is to use sp_refreshsqlmodule, which updates the dependency and other metadata for a procedure, function, or view. The following generates EXEC statements:[code]SELECT 'EXEC sp_refreshsqlmodule N''' + s.[name] + '.' + o.name + ''';' AS [stmt] , s.[name] AS [schema_name] -- schema name , o.[name] AS [object_name] -- procedure, function, or view name , o.[type] AS [object_type] -- type (P, FN, IF, TF, or V)FROM sys.objects oINNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]WHERE o.[type] IN ('FN','IF','TF','P','V')ORDER BY o.[type] , s.[name] , o.[name][/code]Copy and paste the first column into the editor and execute them, or you could use a cursor instead of the copy/paste method. sp_refreshsqlmodule raises an error if the object is no longer valid (for example a view referencing a column that has been dropped from a table).After executing sp_refreshsqlmodule, you can then use sys.sql_dependencies and sp_depends.

RE: When sp_depends fails

Nate Schmidt — Tue, 27 May 2008 11:20:56 GMT

[quote][b]Jim Russell (5/27/2008)[/b][hr]Thanks. You might want to do something to avoid also listing references to tables that include the target name with a prefix or suffix; e.g. 'MyTable' will also return references to 'MyTableOld', 'IndexedMyTable', etc.[/quote]Not completely to your point, but I found that adding a space to the end of the table name ('MyTable ') eliminated the suffixes. As to prefixes, if I always fully qualified my tables ([database].[schema].[tablename], I suppose putting a period in the front would work...but I don't. Obviously, naming convention comes to play in a hurry too.Nate

RE: When sp_depends fails

Jim Russell-390299 — Tue, 27 May 2008 06:13:05 GMT

Thanks. You might want to do something to avoid also listing references to tables that include the target name with a prefix or suffix; e.g. 'MyTable' will also return references to 'MyTableOld', 'IndexedMyTable', etc.

When sp_depends fails

Jano Petras — Thu, 10 Apr 2008 20:39:17 GMT

Comments posted to this topic are about the item [B]When sp_depends fails[/B]