last used table list - 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 2008 Forums

Transact-SQL (2008)

last used table list

Author

Topic

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-31 : 09:35:20

Hi to all,

I am using below qurey to get last used table , i am getting result also. If i run the qurey using master database in ssms by using different database _id in qurey i am getting wrong result. I am not understanding why ,please help me with this.

SET

ANSI_WARNINGS OFF

;

SET

NOCOUNT ON

;

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

= 8
)

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;

Thanks

Manju

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 09:39:11

The object resolution (object_name() for instance) will be in the database the query is running in whereas the id will be for the database you are querying (id 8 in this case).
You would probably have to use dynamic sql to make it run in any database.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-31 : 09:53:06

Hi NIgelriveet,
Thanks for reply could you please tell me how to do that

Thanks

Manju

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 10:20:47

Note - database name test is used twice.

declare @s varchar(max)
select @s = 'use test
;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(''Test'' )
)
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;
'
exec (@s)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 10:22:12

Version with table name as a variable

declare @s varchar(max)
declare @dbname varchar(200) = 'test'
select @s = 'use ' + @dbname + '
;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(''' + @dbname + ''' )
)
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;
'
exec (@s)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 10:28:40

Actually - just noticed that dbid is a parameter for the object functions. Has that always been there?

declare @dbid int
select @dbid = DB_ID('test')

;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 = @dbid
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id],@dbid),
[Table_Or_View] = OBJECT_NAME([object_id],@dbid),
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],@dbid),
OBJECT_NAME([object_id],@dbid)
ORDER
BY 1,2;

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-11-02 : 02:03:24

Hi Nigelrivet,

Thanks a lot i got what i need,from your query but i want one more column in that qurey thats 'user_scans' i added that column in qurey but i am getting this error
:Msg 8159, Level 16, State 1, Line 40
'x' has fewer columns than were specified in the column list.

Thanks

Manju

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources