Search for a table/string within job steps, dts, database, stored procedures etc

Hi All :

A couple of tables have been identified to be deleted. My job is to
find if it is at all used.
On searching the web, i found a proc to search for a string within all
databases in a server.
using system sproc : sp_msforeachdb

it searches for a string in
views, sprocs, functions, check constraints, defaults, foreign key,
scalar function, inlined tablefunction, primary key, 'Replication
filter stored procedure, System table, Table function, Trigger, 'User
table, 'UNIQUE constraint''Extended stored procedure'

So it is pretty extensive. But i dont think it is covering the code
within execsqltasks in DTS, and tsql code within JOB STEPS. Those are
the two more places where code exists in my server.

If any of you have done so in the past, do let me know if there is a
system stored proc or code that you have written, to do the same

thanks
RS

Link to the above procedure

http://www.sql-server-performance.com/ak_find_sql_server_dat abase_objects.asp

Re: Search for a table/string within job steps, dts, database, stored procedures etc

(rshivaraman@gmail.com) writes:
> A couple of tables have been identified to be deleted. My job is to
> find if it is at all used.
> On searching the web, i found a proc to search for a string within all
> databases in a server.
> using system sproc : sp_msforeachdb
>
> it searches for a string in
> views, sprocs, functions, check constraints, defaults, foreign key,
> scalar function, inlined tablefunction, primary key, 'Replication
> filter stored procedure, System table, Table function, Trigger, 'User
> table, 'UNIQUE constraint''Extended stored procedure'
>
> So it is pretty extensive. But i dont think it is covering the code
> within execsqltasks in DTS, and tsql code within JOB STEPS. Those are
> the two more places where code exists in my server.
>
> If any of you have done so in the past, do let me know if there is a
> system stored proc or code that you have written, to do the same

If you don't have all your code under version control, this is about
an impossible task. Keep in mind that in SQL 2000, the code in syscomments
is chopped up in pieces of 4000 characters, and the table name may be
split in two parts.

One strategy may be to rename the tables, and then see how much explodes.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx