Posted by Erland Sommarskog on 05/16/07 22:49

(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]