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 @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    USE @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

    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.

    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

    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.

    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

    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

    Awesome. My journey completes!!! Thanks you Thom A and Avi1. Thank you!!

  • Michael_O - Tuesday, October 10, 2017 9:32 AM

    Thom A - Tuesday, October 10, 2017 9:01 AM

    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

    Awesome. My journey completes!!! Thanks you Thom A and Avi1. Thank you!!

    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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • Jeff Moden - Wednesday, October 11, 2017 11:02 PM

    Michael_O - Tuesday, October 10, 2017 9:32 AM

    Thom A - Tuesday, October 10, 2017 9:01 AM

    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

    Awesome. My journey completes!!! Thanks you Thom A and Avi1. Thank you!!

    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
    ;

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply