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
ASKER CERTIFIED SOLUTION
Steve Wales
Senior Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
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