troubleshooting Question

Running SQL against all dbs

Avatar of raymurphy
raymurphy asked on
Microsoft SQL Server 2005
2 Comments1 Solution370 ViewsLast Modified:
I run the following SQL to get details of the duration in days between a procedure's creation date and its last modified date :

-- Calculate the duration in days between each procedures Modification date and Current Date
-- Issue this against the required database
SELECT
   name,
   CONVERT(char(12),create_date,9) AS CREATED, CONVERT(char(12),modify_date,9) AS MODIFIED,  
   -- Specifically show duration between Modified Date and Current Date
   DATEDIFF(DAY, modify_date, getdate()) AS DaysSinceMod,
   CASE
        WHEN DATEDIFF(DAY, create_date, modify_date) = 0 THEN 'NEVER been Modified'
        WHEN DATEDIFF(DAY, modify_date, getdate()) BETWEEN 1 AND 30 THEN '30 Days (or less) ago'
        WHEN DATEDIFF(DAY, modify_date, getdate()) BETWEEN 30 AND 60 THEN '30-60 Days ago'
        WHEN DATEDIFF(DAY, modify_date, getdate()) BETWEEN 60 AND 90 THEN '60-90 Days ago'
        WHEN DATEDIFF(DAY, modify_date, getdate()) BETWEEN 90 AND 120 THEN '90-120 Days ago '  
        WHEN DATEDIFF(DAY, modify_date, getdate()) > 120 THEN 'More than 120 Days ago'  
    END
    AS DurationSinceLastModified
FROM sys.objects
WHERE type = 'P'
AND   name NOT LIKE 'dt_%'

This works fine when I run it directly against a specific database, and gives me the following typical output :

name            CREATED         MODIFIED        DaysSinceMod   DurationSinceLastModified
proc1              Apr  5 2012       Apr  5 2012       454             NEVER been Modified
proc2              May  3 2012       Nov 30 2012       215             More than 120 Days ago
proc3              Jun 18 2008       Jun 27 2013       6             30 Days (or less) ago

I'd now like to wrap this up somehow, so that I can run this statement against ALL databases on my instance, so that I end up with output along the lines of :

DATABASE BEING CHECKED - DBNAME1

name            CREATED         MODIFIED        DaysSinceMod   DurationSinceLastModified
proc1              Apr  5 2012       Apr  5 2012       454             NEVER been Modified
proc2              May  3 2012       Nov 30 2012       215             More than 120 Days ago
proc3              Jun 18 2008       Jun 27 2013       6             30 Days (or less) ago

DATABASE BEING CHECKED - DBNAME2

name            CREATED         MODIFIED        DaysSinceMod   DurationSinceLastModified
proc4              Apr  5 2012       Apr  5 2012       454             NEVER been Modified
proc5              May  3 2012       Nov 30 2012       215             More than 120 Days ago
proc6              Jun 18 2008       Jun 27 2013       6             30 Days (or less) ago

... etc ....

I'm presuming I'll end up needing to use sp_MSforeachdb, but I'd appreciate some tips on how best to do this.

Thanks

Ray
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros