Timeline for How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 19, 2013 at 15:16 | comment | added | Kenneth Fisher |
@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.
|
|
| Oct 19, 2013 at 8:55 | comment | added | gbn | @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. | |
| Oct 18, 2013 at 21:26 | comment | added | Kenneth Fisher |
@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.
|
|
| Nov 16, 2011 at 18:51 | comment | added | gbn | @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... | |
| Nov 16, 2011 at 15:03 | vote | accept | farp332 | ||
| Nov 16, 2011 at 14:32 | comment | added | datagod | @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 | |
| Nov 16, 2011 at 14:29 | comment | added | gbn | @Nick Chammas: nope, this is well known as being unreliable | |
| Nov 16, 2011 at 14:27 | comment | added | Nick Chammas | Can't the fellow also right-click on the target table and hit "Show Dependencies" (or some text to that effect)? | |
| Nov 16, 2011 at 13:01 | history | answered | gbn | CC BY-SA 3.0 |