April 8, 2010 at 8:31 pm
Hi all
We have few tables created in the database and owner is DBO. However we would like to delete the tables if it is not used.
Can someone please let me know how we can find this information. I have checked the Sysobjects and sysusers table and it has the uid as 1 for dbo
SELECT so.name AS 'ObjectName',su.name AS 'OwnerName'
FROM sysobjects so, sysusers su
where so.uid = su.uid and so.name = 'temp100'
ORDER BY 2
Thanks
Vani
April 8, 2010 at 10:39 pm
I dont think the information you are looking for is stored somewhere in the database.
You can set up auditing or DDL triggers to capture this information.
April 9, 2010 at 7:41 am
vani_r14 (4/8/2010)
we would like to delete the tables if it is not used.
What do you mean by "Not used" ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 9, 2010 at 7:44 am
Try this
use cvent_dba
go
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply