March 3, 2011 at 9:46 am
Hello all,
I am a new dba for a small bank. first time posting on a forum like this. We are moving numerous tables to a newly created database and I need to find out all the processes that are touching these tables including ssis packages? Any helpfull tips would be appreciated 🙂
Thanks!
March 3, 2011 at 10:23 am
It's easy to find local stored procedures, views, et al, that hit those tables, by querying sys.sql_modules and checking the definition column. That'll get you started.
To check for all access to them, run a trace on all DML statements issued to the server, both those that are their own batches and statements within stored procedures. sp_trace_create and the related subjects, on MSDN or in BOL, will tell you how to set up such a trace. Once you've gathered data for a long enough period of time, you can query that for the table names you want.
You can (should) also leave either views or synonyms behind in the current database, pointing at the tables in the new database (possibly through a linked server if you're moving them that far). That way, if you miss anything, it will simply be slowed down, not broken.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 3, 2011 at 10:48 am
If your SSIS package are stored in SQL Server you can query the contents of them using LIKE with wildcards the same way you can query sys.sql_modules for references in stored procedures. Take a look at this article: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70011/%5B/url%5D
If you store your SSIS packages on disk like I do then you can use any text editor with a "Find in Files" search feature to scan your SSIS library. SSMS has a "Find in Files" feature but I find the design a little lacking (requires too many clicks). I use TextPad for "Find in Files" tasks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 11:21 am
You guys are awesome! Thank you that was some great information. I very much appreciate it and look forward to helping others when I get the opportunity. I also just installed RedGates SQL Search tool which can parse through strings of code and its fast at doing it! Thank you once again gentlemen 🙂
March 4, 2011 at 6:32 am
Just keep in mind that, no matter how well you search locally on your server, you can't be sure you've got everything. That's where a trace is critical.
Other servers can reference tables in a local server, if they either have linked servers or use the various ad hoc links (OpenRowset, OpenRecordset). SSIS packages on other servers can too. So can code completely outside of SQL Server environments, like web pages with inline SQL code. A trace will find those, while searching your server won't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply