expecting ID or quoted_ID. Help!
-
error i'm getting from the below query: Incorrect syntax near '@DatabaseName'.
DECLARE @DatabaseName as NVARCHAR(200);
DECLARE DatabaseCursor CURSOR FOR
SELECT Name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseNameWHILE @@FETCH_STATUS = 0
BEGINUSE @DatabaseName
WITH agg AS
(
SELECT
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
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;
END
CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor; -
The syntax "USE @DatabaseName" isn't valid. You can't provide a variable as part of a statement, you'll need to use dynamic SQL.
Considering, however, that you're doing a process for each database, have a look at the (undocumented) procedure sp_MSforeachdb.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, October 9, 2017 8:02 AM
Thanks for your contribution. I was able to use sp_msforeachdb but the result set isn't presented in a 'reportable' manner. Also hard to read if i have at least 70 DBs on this instance
-
What do you mean by "isn't reportable"? What's wrong with it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
If you want to get the data in as single resultset, then rather then using select, insert the data in a temp table in the query used in sp_MSforeachdb and then select from that table. insert database name as well in the table.
- Avi1 - Monday, October 9, 2017 8:51 AM
Half way there now. Just need to figure out how to get database name in there
-
The query that got me the above resultset
Declare @unuseddbs Table([last_read] NVARCHAR(255), [last_write] NVARCHAR(255) )
INSERT INTO @unuseddbs
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; WITH agg AS
(
SELECT
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
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write)'SELECT * FROM @unuseddbs
-
You can reference the db name more than once in your query using ?. Thus:
Declare @unuseddbs Table(dbname varchar(100), [last_read] NVARCHAR(255), [last_write] NVARCHAR(255) )
INSERT INTO @unuseddbs
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; WITH agg AS
(
SELECT
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 ''[?]'' AS DBName,
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write)'SELECT * FROM @unuseddbs
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, October 10, 2017 9:01 AM
Awesome. My journey completes!!! Thanks you Thom A and Avi1. Thank you!!
- Michael_O - Tuesday, October 10, 2017 9:32 AM
Your journey is just getting started. 😉 I'm pretty sure that you don't need any loops or other gizmos to determine if a database has been used based on index/heap usage. The sys.dm_db_index_usage_stats view has a "database_id" column in it and it covers all databases. You also have to remember that just because it appears to not be used, it still could be because the sys.dm_db_index_usage_stats only shows what's been used since the last restart of the SQL Server service.
Here's the code that, if I'm thinking correctly, replaces all of the code you've written so far and incorporates the database name, to boot.
SELECT DbName = db.name
,Last_Read = MAX(r.Last_Read)
,Last_Write = MAX(st.last_user_update)
FROM sys.dm_db_index_usage_stats st
RIGHT JOIN sys.databases db ON st.database_id = db.database_id
CROSS APPLY (VALUES (last_user_seek),(last_user_scan),(last_user_lookup))r(Last_Read)
GROUP BY db.name
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. You might want to exclude system databases from the return just to be safe.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, October 11, 2017 11:02 PM
oh wow, that very important line to note : "since the last reboot"!
I just ran that query on one instance which i restarted this morning and i got lots of NULLS, you're so right. 🙁
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply