December 29, 2010 at 7:48 am
Hi,
Can any one tell me how to find the tables which are not being used in a perticular database, this is because i want to clean up the database if the tables are no longer in use.
I appriciate for your help.
Thanks
Rahul.
December 29, 2010 at 9:09 am
If you definition of unused is that it has zero records, you can run a query to determine this.
Otherwise, you'll probably have to run a server-side trace to track what objects are being hit, and determine unused tables over time.
I'm not that knowledgeable on the DMVs - there might be something there to get this information for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 29, 2010 at 1:43 pm
There is no completely reliable method for retrieving this information. Almost any method you use can be spoofed because a given table is only accessed once a month/quarter/year and if your server restarts over that period, you can lose access to the data that can provide this.
Best bet, know the system. Check all dependencies within the procedures & views and triggers, check the reports, check the applications (if they're using ad hoc TSQL).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 29, 2010 at 1:51 pm
Even empty tables may be in use - they can either be staging or temporary tables truncated after each run; on the other hand I've seen populated tables go deprecated and left in the database forever.
As previous poster says - no bullet proof method to be 100% sure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2010 at 3:05 pm
Would it help to see if a table is used in a sproc, view or function?
Then you could use sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities and sys.sql_expression_dependencies.
This will at least give you the list of tables being referenced.
The remaining tables may or may not be in use. A tricky scenario is a frequent cross-database call or a direct call from an application, since this won't show up in the dependency query.
A profiler trace as mentioned before might help to narrow the list down even further.
Except for several "brute-force" methods I don't think there is any way to clearly identify the unsued tables.
January 10, 2011 at 12:41 pm
Thank you all.........:-)
January 26, 2011 at 1:00 pm
In my search i found out the following querys................
The query 1 will tell you the tables which are not being used by any of the stored procedures, functions, views........ etc.
Infact you can take the result as the unused tables in the database but make sure to cross check with your other team members to find out that the tables are not being used, as the tables may be a flag tables and may be some tables used by the other databases in the network.
And it is also important to find out the tables are last accessed in the database (query2).
/*finding tables which are not being used by any of the stored procedures, views, functions.....etc*/
select
t.name,
sys.objects.name foundin,
sys.objects.type_desc
from
sys.objects t
left outer join
sys.sql_modules
inner join
sys.objects
on
sys.objects.object_id = sys.sql_modules.object_id
on
sys.sql_modules.definition like '%' + t.name + '%' where t.type = 'U'
and sys.objects.name is null
order by
t.name,
type_desc,
foundin
/*Finding the the date and time of the tables in the database which are last accessed*/
WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = ‘U’
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
I think it may be helpfull to some extent in the process of cleaning up the database.
Thanks,
Rahul.
January 26, 2011 at 4:40 pm
Just know that if you've rebooted the machine or restarted the SQL Server instance, all information is lost from the index usage DMV. There's absolutely no guarantee that will show you what has been accessed. The other query doesn't take into account client side code or reports, neither of which will have code stored on the database.
Determining which tables can be safely removed is hard.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply