Set and Use database name - 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 2005 Forums

Transact-SQL (2005)

Set and Use database name

Author

Topic

JimmyH
Starting Member

3 Posts

Posted - 2013-09-04 : 08:49:05

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-04 : 09:28:19

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-04 : 09:37:29

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

JimmyH
Starting Member

3 Posts

Posted - 2013-09-04 : 09:50:22

It was. Thank you.

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-05 : 00:34:46