How to find unused objects? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to find unused objects?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-09-30 : 15:04:31

As part of our automated testing I'd like to find all Tables, SProcs, Triggers and Functions (anything else??) that are not references so that we can either:

1) Add more tests to "cover" the objects that were untested
or
2) Drop the genuinely unreferenced objects from the database

Ideas please?

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-30 : 19:07:11

Check sysreferences (but won't catch dynamic sql).
Put triggers on tables to log events (but won't catch reads).
Run a massive profiler trace (catches everything for plankton to blue whales. You sort it out...).

"Once you establish possibility, all that remains are desire and determination."

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-30 : 20:40:43

I've never used this myself, but it's exactly what you want if you can drop $295
http://www.red-gate.com/products/SQL_dependency_tracker/index.htm

Kristen
Test

22859 Posts

Posted - 2006-10-01 : 02:00:04

sysreferences - did you mean sysdepends? I've never found sysdepends reliable enough after lots of iterations of DROP/CREATE

As an aside: we have considered changing our code style of:


IF SProc EXISTS
    DROP
CREATE PROCEDURE


IF SProc DOES NOT EXISTS
    CREATE One Line Dummy Procedure
ALTER PROCEDURE

so as to have less disruption to sysdepends.

"Run a massive profiler trace"

I only make very basic use of Profiler, so excuse my ignorance:

Won't that just tell me that "EXEC MySProc" happened - rather than the fact the MySproc did SELECT * FROM MyTable ?

Its the fact that MyTable was referenced that I need (well, I also need to know that MySproc was executed too ...)

"Red-Gate SQL Dependency Tracker"

Interesting product, thanks. But I reckon it just looks at the source code, which isn't quite the same as whether everything got "exercised" in a specific situation - in my case its our Test System's coverage I'm wanting to prove.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-01 : 10:36:55

Instead of doing the trace thingy, you could create a process that queries syscacheobjects,
and harvest all the objects in the cache.

rockmoose

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-01 : 11:09:14

Using profiler you can capture the sql statement which was executed (dynamic, or procedure call), pipe the results to a file, and search it for object names. Tedious.
And while all of these methods will tell you which objects ARE being used, they cannot tell you which objects AREN'T being used (by somebody, somewhere, sometime).
You can try the "Scream Test". Rename suspect objects by prepending "X" to the object names. Then listen for screaming from your Accounting Department, Human Resources, Engineering Department, or wherever, and you will know who is using the object.

"Once you establish possibility, all that remains are desire and determination."

pootle_flump

1064 Posts

Posted - 2006-10-01 : 17:31:01

quote:
Originally posted by blindman

And while all of these methods will tell you which objects ARE being used, they cannot tell you which objects AREN'T being used (by somebody, somewhere, sometime).

INSERT all objects into table, left outer join ObjectsReferencedBySomethingElseTable WHERE ObjectsReferencedBySomethingElseTable.objName is NULL?

I've made use of a script to find object dependancies across databases (I can tidy and post if you like but I imagine you are more than capable of writing one yourself). I think it is available on the web since the code dosn't look like mine but I can't find it the anywhere (I forgot to include the URL in the script too

)

And yes - it makes use of sysdepends but I only have limited dynamic SQL and can easily account for all the objects referenced by dynamic sql.

I am sure you have posted your skeleton sproc code previously but I can't recall if this includes a logging of sproc execution.... (I don't post often but I read frequently - I do know that your sprocs typically appear very thorough indeed

). Anyway - I have found sproc logging as Brett advocates very useful for this sort of thing (as well as for other information).

I am intrigued and somewhat concerned by "I've never found sysdepends reliable enough after lots of iterations of DROP/CREATE" though. I didn't know that - have you a reference?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-01 : 19:05:54

I like the scream-test

Beware of first day of the month routines, they tend to scream.

Ah, a very nasty bad-practice I have found, is that when code is changed,
sometimes the old objects are just -- COMMENTED AWAY!,
which will cause any search scripts to report use of old objects.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-01 : 19:46:55

quote:
Originally posted by pootle_flump
I am intrigued and somewhat concerned by "I've never found sysdepends reliable enough after lots of iterations of DROP/CREATE" though. I didn't know that - have you a reference?

Haven't you ever used a generated ddl script and received a message something like "Objectxxx will not be added to sysdepends because the object references does not exist", due to the script not being generated in the order of object dependency?

"Once you establish possibility, all that remains are desire and determination."

pootle_flump

1064 Posts

Posted - 2006-10-01 : 20:19:35

quote:
Originally posted by blindmanHaven't you ever used a generated ddl script and received a message something like "Objectxxx will not be added to sysdepends because the object references does not exist", due to the script not being generated in the order of object dependency?

Good point. Yes I have.

I ASSuMEd that Kristen was talking about atomic DROP\ CREATES. My inference was that Kristen's observation was to do with valid dependencies being missed rather than dependencies not being recognised because the dependent objects don't actually exist. I guess the key is in "generated" - I figured Kristen was talking about DBA approved\ generated code.

I kind of hope your reading is correct and not mine. Hmm - so many emoticons and none that quite seem to capture my meaning. Maybe this one:

Nope - that just makes me look like I'm on drugs.

EDIT - um - spelling

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-01 : 20:46:49

Not sure if this helps

http://www.pinpub.com/ME2/Audiences/dirmod.asp?sid=&nm=&type=Publishing&mod=Publications%3A%3AArticle&mid=8F3A7027421841978F18BE895F87F791&AudID=3A23D70D325A41A9AC644714EED86750&tier=4&id=CE9E01AE99334E1CBDC32C150AE6BE08

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-01 : 22:34:53

When I really want to know if an object is unused, I rename it and wait to see if anyone screams or if applications break.

Kind of a crude method that only works to confirm they are unused, but doesn't really help you find unused objects.

CODO ERGO SUM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-02 : 02:27:28

for each sproc, add a line that will insert a record in a table in your admin database when called

after a period of time (cover maintenance periods also), you can check back to the table and see which objects are not referenced

just an idea...

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 07:16:33

It looks blindingly obvious then ...

... we already have "Log Me" code in all our Sprocs (well, there are 3 or 4 that are called so often that we don't do that, but I a) know what they are and b) they are that way because they are being used!)

And I can be pretty sure that all the tables are in use - we only have about 350 - 400 tables in our application.

Additionally and record inserted/updated should change its UpDate date - so looking at the most recent UpdateDate should tell me something. Most of our tables have Audit tables, populated by Triggers, so newly arrived Delete entries tell me about deletions - and newly arrived Updates tell me about that too - if the UpdateDate doesn't. (We don't store INSERTs in the Audit tables, to save space, only UPDATEs and DELETEs)

VIEWs are a bit more hazy - we have very few of those. Might need a Scream Test for that ...

Triggers follow on from a) whether we have a table and b) whether the table was Updated/Deleted from.

Yup, I reckon that is the basis for considerably more than I realised we could do - silly me!

As this is for our Automated Test the Scream Test is OK - I reckon we can:

1) Run the tests
2) Analyse all Used Objects from the Logs
3) Rename everything else to "_NU_yymmdd" - (Not used) - but only if not already named "%_NU_%"
4) Rename to remove the "_NU_" marker from "Scream" test failures - i.e. Re-instate any missing objects
6) Repeat until no Scream Failures

Kristen

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 07:31:00

"I am intrigued and somewhat concerned by "I've never found sysdepends reliable enough after lots of iterations of DROP/CREATE" though"

Create an SProc (MySP1)
Then a second that calls it (MySP2)
Check SYSDEPENDS
Then DROP and reCREATE the first Sproc without recreating the second (we never bother recreating the second one, if it hasn't changed, but maybe we are unique in that regard?)
Re-check SYSDEPENDS


CREATE PROCEDURE MySP1
AS
	SELECT TOP 10 name
	FROM	sysusers
GO

CREATE PROCEDURE MySP2
AS
	EXEC MySP1
GO

SELECT TOP 100
	[Name] = LEFT(O1.name, 10),
	[Type] = O2.type,
	[Depends] = LEFT(O2.name, 10),
	depnumber
--	, *
FROM	sysobjects AS O1
	JOIN sysdepends AS D
		ON D.ID = O1.ID
	JOIN sysobjects AS O2
		ON O2.ID = D.depid
WHERE	    O1.name IN ('MySP1', 'MySP2')
--	AND O1.type = 'P'

GO
DROP PROCEDURE MySP1
GO

CREATE PROCEDURE MySP1
AS
	SELECT TOP 10 name
	FROM	sysusers
GO
SELECT TOP 100
	[Name] = LEFT(O1.name, 10),
	[Type] = O2.type,
	[Depends] = LEFT(O2.name, 10),
	depnumber
--	, *
FROM	sysobjects AS O1
	JOIN sysdepends AS D
		ON D.ID = O1.ID
	JOIN sysobjects AS O2
		ON O2.ID = D.depid
WHERE	    O1.name IN ('MySP1', 'MySP2')
--	AND O1.type = 'P'

GO

DROP PROCEDURE MySP1
GO
DROP PROCEDURE MySP2
GO

Name       Type Depends    depnumber 
---------- ---- ---------- --------- 
MySP1      S    sysusers   3
MySP2      P    MySP1      1

(2 row(s) affected)

Name       Type Depends    depnumber 
---------- ---- ---------- --------- 
MySP1      S    sysusers   3

(1 row(s) affected)

The reference to MySP2 calling MySP1 has gone :-(

EDIT: I tried changing the reCREATE of MySP1 to use an ALTER instead, but its the same result, so that means of preserving SYSDEPENDS was a flawed thought [:-(]

Kristen

pootle_flump

1064 Posts

Posted - 2006-10-02 : 07:56:50

Thanks Kristen - that makes it perfectly clear.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:41:18

quote:
Originally posted by Michael Valentine Jones

When I really want to know if an object is unused, I rename it and wait to see if anyone screams or if applications break.

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-03 : 03:17:17

quote:

VIEWs are a bit more hazy - we have very few of those. Might need a Scream Test for that ...

does that mean you're not using sprocs or functions to access all of the views Kristen?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 03:34:17

SProcs and Function access the tables .... we only have half a dozen views, and don't really use them that much ... maybe I need enlightening?

Lets say I have an Order Item table. It has a parent Order table which joins to Customer. The Order Item table joins to Product, and Price List (also using a Price List code in Customer table).

So I could have a view:


SELECT OrderItem.Col1, OrderItem.Col2, ...
       Order.Col1, Order.Col2, ...
       ...
FROM   OrderItem AS OI
       JOIN Order AS O
           ON O.OrderID = OI.OrderID
       JOIN Customer AS C
           ON C.CustID = O.CustID
       JOIN Product AS P
           ON P.ProductID = OI.ProductID
       JOIN PriceList as PL
           ON PL.ProductID = OI.ProductID
          AND PL.PriceListID = C.PriceListID

and use the view wherever use the OrderItem table (because I probably want one or other of the Joined tables, rarely all of them, and some of the columns, never all of them?

This would be to a) avoid having to type the JOINs every time and b) centralise changing of the JOINs if that becomes necessary.

However, whenever I add a column to a table, or another associated table, I will need to keep the VIEW in step.

And what I really don't know is what the Optimiser makes of all the redundant tables and columns and, more importantly for me, how long it takes the Optimiser to throw away the redundant stuff.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources