tri-j enterprises, inc.

...because good software just matters

  • Increase font size
  • Default font size
  • Decrease font size
Home development database
database

comments

E-mail Print PDF

so i was bumbling around a few years ago and found in enterprise manager for sql server 2000 a feature that shows dependencies.  to the life of me, i can't remember today where it was but i know its somewhere.  you can't imagine how frustrating it is to need to know this info and it not be accurate.  so i went searching and found this neat little table called syscomments.  what this table holds is the text to objects stored inside sql server.  so i wanted to find all stored procedures that referenced a certain table.  so i started bumbling around with select statements.  following is what i ended up with.

SELECT DISTINCT

        OBJECT_NAME(id)

FROM    syscomments

WHERE   text LIKE '%employee%'

ORDER BY OBJECT_NAME(id)

so what does the above blob of a sql statement do?  lets walk through it.  first things first. the columns used here are id and text.  the id is the object id in sql.  the text column holds all the text for the objects.  i used an internal sql function with the object id to return the "pretty" object name.  i then sorted by that and also added a distinct clause.  the reason for distinct is that if the search text appears 50 times in one procedure, with distinct, it will only appear once in the list.  the search text is employee and i used percents for wildcards because i wanted anything before and after the word employee.

hope this helps when you need to find something special. 

Last Updated on Monday, 01 March 2010 15:49
 

database

E-mail Print PDF

we can develop all sorts of back end database solutions that range from stored procedures to views to triggers, etc.  we can develop in microsoft sql server, sybase, mysql, and also ibm unidata.  please come back soon to see some examples.

Last Updated on Thursday, 30 April 2009 14:37
 



search

whos online

We have 14 guests online

stats

OS : Linux m
PHP : 4.4.9
MySQL : 5.1.73-log
Time : 05:25
Caching : Disabled
GZIP : Disabled
Members : 50
Content : 55
Web Links : 183
Content View Hits : 72879

Powered by WebRing.