April 10, 2008 at 8:39 pm
Comments posted to this topic are about the item When sp_depends fails
May 27, 2008 at 6:13 am
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.
May 27, 2008 at 11:20 am
Jim Russell (5/27/2008)
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.
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
May 27, 2008 at 1:18 pm
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:
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 o
INNER 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]
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.
July 17, 2008 at 11:41 am
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 sp
select o.name+'.'+sc.name
from sys.objects o
join sys.columns sc
on sc.object_id = o.object_id
join syscomments c
on 1 = 1 --don't want to join
where 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.name
order 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.
September 4, 2008 at 8:47 am
Does not work
SELECT * FROM information_schema.routines r
WHERE charindex('Dbo.People2', r.ROUTINE_DEFINITION)>0
Does work
SELECT * FROM information_schema.routines r
WHERE charindex('People2', r.ROUTINE_DEFINITION)>0
Nate Schmidt
fully qualified my tables ([database].[schema].[tablename], I suppose putting a period in the front would work...but I don't
Tried that and it does NOT work
My 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.
September 4, 2008 at 9:54 am
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 + 1
then 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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply