x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

September 2006 Blog Posts

How do I find all the tables referenced by Stored Procedures or Functions

Like this SELECT o.name, t.TABLE_NAME, c.text   FROM syscomments c   JOIN sysobjects o     ON c.id = o.id   JOIN INFORMATION_SCHEMA.Tables t     ON  c.text LIKE '%'+t.TABLE_NAME+'%'  

posted @ Friday, September 22, 2006 12:32 PM | Feedback (6) | Filed Under [ SQL Server ]

Stored Procedure Logging

Every so often, someone asks, "How do I know who executed a SQL Statement against my database". Well you can either have SQL Profiler running all the time (which can be very expensive), or you can use Lumingent's Log Explorer. I have taken a different tack lately. Any Access to a database I am supporting will be done ONLY Through stored procedures.  OK, that's not "lately", but the part I've added is that the developers MUST call the sproc below.  What this does is to log every stored procedure call.  I now have statistics as to what's being called when, and how long...

posted @ Thursday, September 21, 2006 9:38 AM | Feedback (4) | Filed Under [ SQL Server ]

Finding Trade and Receipt fallout

A director came to me asking if a set based approach could be used to find the fallout for trades and receipts.  Even though they handed the work off already.  The developer  used COBOL to compare the 2 file and did "spin-up" processing to match a trade to a receipt.  Only probalem is that is totally arbitrary since the "key" was basicall generic and did not exactly marry the receipt to the trade.  So their "fallout" was based on LILO.  They considered everything else as a match. What they really needed to do was identify target population that have "fallout" and...

posted @ Wednesday, September 20, 2006 1:31 PM | Feedback (4) | Filed Under [ SQL Server ]

sp_depends for DB2

Well, there really isn't anything that I know of that is like sp_depends for DB2 z/OS Version 7.2.  Hopefully V8 will alot more features...but for Now you have to interogate the catalog.  So this is how you do it....     SELECT DISTINCT NAME,DNAME,BNAME                       FROM SYSIBM.SYSPACKDEP D                       INNER JOIN SYSIBM.SYSPACKSTMT S                              ON D.DCOLLID = S.COLLID AND D.DNAME = S.NAME        AND D.DCONTOKEN = S.CONTOKEN                       WHERE BQUALIFIER = 'AXHRSPDA'                          AND BNAME IN('POSITION_TREE')                   ORDER BY NAME,DNAME,BNAME                          ;                                                    And this new editor for posting is pretty neat...lots of features...like background color...and for anyone not familiar with the mainframe...that's what I...

posted @ Wednesday, September 20, 2006 11:21 AM | Feedback (0) | Filed Under [ DB2 ]

Powered by:
Powered By Subtext Powered By ASP.NET