SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Set and Use database name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JimmyH
Starting Member

United Kingdom
3 Posts

Posted - 09/04/2013 :  08:49:05  Show Profile  Reply with Quote
Trying to pull information from several databases and I am using Cursor to loop through the databases but it is primarily falling over on below.

This works and returns master

DECLARE @JH VARCHAR (128)
set @JH = (select top 1 name from sys.sysdatabases)
print @JH

This doesn't work. Error is "Could not locate entry in sysdatabases for database '@JH'"

DECLARE @JH VARCHAR (128)

set @JH = (select top 1 name from sys.sysdatabases)

USE [@JH]
Go

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
SELECT a.database_id, b.name, a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update
FROM sys.dm_db_index_usage_stats AS a INNER JOIN
sys.sysdatabases AS b ON a.database_id = b.dbid
WHERE
database_id = DB_ID()
)
SELECT
name = MAX(name),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT name,last_user_seek, NULL FROM agg
UNION ALL
SELECT name,last_user_scan, NULL FROM agg
UNION ALL
SELECT name,last_user_lookup, NULL FROM agg
UNION ALL
SELECT name, NULL, last_user_update FROM agg
) AS x (Name,last_read, last_write);


bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/04/2013 :  09:28:19  Show Profile  Reply with Quote
You can't use the following
USE [@JH]
GO


--Is this you want?
SELECT a.database_id, b.name, MAX(a.last_user_seek) last_user_seek, MAX(a.last_user_scan) last_user_scan
,MAX(a.last_user_lookup) last_read, MAX(a.last_user_update) last_write
FROM sys.dm_db_index_usage_stats AS a INNER JOIN
sys.sysdatabases AS b ON a.database_id = b.dbid
GROUP BY a.database_id, b.name

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/04/2013 :  09:37:29  Show Profile  Reply with Quote
Here you need only DB_ID()...
WITH agg AS
(
SELECT a.database_id, b.name, a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update
FROM sys.dm_db_index_usage_stats AS a INNER JOIN
sys.sysdatabases AS b ON a.database_id = b.dbid
WHERE
database_id = DB_ID()
)
SELECT
name = MAX(name),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT name,last_user_seek, NULL FROM agg
UNION ALL
SELECT name,last_user_scan, NULL FROM agg
UNION ALL
SELECT name,last_user_lookup, NULL FROM agg
UNION ALL
SELECT name, NULL, last_user_update FROM agg
) AS x (Name,last_read, last_write);

If you want the above approach only, then get list of DB_ID() in the sys.sysdatabases and pass DB_IDs in the WHERE clause..
Post us the exact requirement... we will give you direction

--
Chandu
Go to Top of Page

JimmyH
Starting Member

United Kingdom
3 Posts

Posted - 09/04/2013 :  09:50:22  Show Profile  Reply with Quote
It was. Thank you.

Looks like i was trying over engineer something quite straight forward.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/05/2013 :  00:34:46  Show Profile  Reply with Quote
quote:
Originally posted by JimmyH

It was. Thank you.

Looks like i was trying over engineer something quite straight forward.


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000