9

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in order to avoid the oversize of the same, I didn't design the DB, I'm just taking the maintenance of an application that use this DB, so I don't know the name of these stored procedures or triggers, what I want to do is locate this stored procedure or trigger, check the code and modify it to leave this "user history" longer on the table.

Someone told me to check the "sysobjects" table, where I can actually see something with the same name of the table, but this is the only information I have been able to retrieve, any advise?

Thank you.

marc_s
  • 8,932
  • 6
  • 45
  • 51
farp332
  • 193
  • 1
  • 4

4 Answers4

6

Search all code using sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules sm
WHERE definition LIKE '%Mytable%'

Or use Red Gate SQL Search which is completely free

Do not use syscomments or INFORMATION_SCHEMA.RUBBISH

gbn
  • 69,809
  • 8
  • 163
  • 243
  • Can't the fellow also right-click on the target table and hit "Show Dependencies" (or some text to that effect)? – Nick Chammas Nov 16 '11 at 14:27
  • @Nick Chammas: nope, this is well known as being unreliable – gbn Nov 16 '11 at 14:29
  • @gbn, I understand that syscomments is for backwards compatability and may be removed from a future release, but can you elaborate on why not to use INFORMATION_SCHEMA? thx – datagod Nov 16 '11 at 14:32
  • @datagod: same why syscomments as always a poor choice: the definition column is nvarchar(4000). This means you may miss a table reference if in string position >4000 or spans the boundary between nvarchar(4000) rows (depends how you handle it, could concat I suppose if you really wanted... – gbn Nov 16 '11 at 18:51
  • @gbn Actually syscomments has multiple rows for an object that goes over the 4000 character limit, making queries using it just as valid as sys.sql_modules. I still recommend sys.sql_modules however since syscomments is depreciated. – Kenneth Fisher Oct 18 '13 at 21:26
  • @KennethFisher:L "making queries using it just as valid as sys.sql_modules" -> feel free to concatenate them. syscomments has never been valid because of the inability to concatenate and search in SQL Server 2000. – gbn Oct 19 '13 at 08:55
  • @gbn no need to concatenate them unless you want the full definition at which point sp_helptext is easier. The object id is the same for each row so a like query off of syscomments where the hit is on the second or even third row of a large piece of code will still return the correct object_id. The only problem may be getting multiple hits for the same object. – Kenneth Fisher Oct 19 '13 at 15:16
4

Try out ApexSQL Search

ApexSQL Search is a FREE SQL Server Management Studio and Visual Studio add-in that, among other features, has the View Dependencies feature. The View Dependencies feature has the ability to visualize all SQL database objects’ relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE)

The View Dependencies feature also allows you to set up and customize the visual dependencies diagram layout, including the relationships that will be presented, the layout and size of the generated diagram, and the drill-down depth of the dependencies

Disclaimer: I work for ApexSQL as a Support Engineer

Milica Medic Kiralj
  • 659
  • 7
  • 7
2

For future reference as of 2008 there is also a DMV that can be used. sys.dm_sql_referencing_entities. I generally prefer it to using SQL_Modules as it avoids false positives among other things. I discussed it here but basically if you have a piece of code like this:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.sql_modules WHERE [definition] LIKE '%ABC%'

You will end up with results for the table ABC, the table ABCLog the view vw_ABC, the stored procedure sp_Update_ABC etc. Also to the best of my knowledge the DMV will handle encrypted SPs as well as unencrypted while the sql_modules method only works with unencrypted SPs.

The DMV version of the same query is this:

SELECT * FROM sys.dm_sql_referencing_entities('dbo.ABC', 'OBJECT')
Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114
1

Also you can use the sys.sql_expression_dependencies catalog view. Use this query:

SELECT 
referencing_object_name = obj.name, 
referencing_object_type_desc = obj.type_desc, 
referenced_object_name = referenced_entity_name
FROM sys.sql_expression_dependencies sd 
INNER JOIN sys.objects obj 
ON sd.referencing_id = obj.[object_id] 
WHERE referenced_entity_name = 'MyTable'
Milica Medic Kiralj
  • 659
  • 7
  • 7