471,067 Members | 1,562 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,067 software developers and data experts.

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.co...se_objects.asp

May 16 '07 #1
1 3759
(rs*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 16 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by todd | last post: by
4 posts views Thread by Axel | last post: by
7 posts views Thread by David Lozzi | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.