1

I am trying to use the following stored procedure to obtain databases from a server. However, I just want the databases created from a month ago. What do I have to do to make this work?

Here is the code:

sp_msforeachdb 'IF ''?'' like ''z%'' 
BEGIN
  print ''?''
END'

I just want the databases that begin with Z that were created during a certain month.

Drew
  • 29,895
  • 7
  • 74
  • 104
Jeff
  • 852
  • 7
  • 17
  • 28

1 Answers1

5

How about this instead.

select name
    from sys.databases
    where name like 'z%'
        and create_date >= dateadd(mm,-1,getdate())
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • the problem is, i'm trying to iterate through several databases and trying not to use a cursor or loop. i've tried your code before within a cursor and it took a while to gather the data I needed. – Jeff Feb 03 '11 at 22:46
  • @Jeff: Not sure I follow. `sp_msforeachdb` *is* a cursor under the hood. You can see this for yourself by running: `sp_helptext 'sp_msforeachdb'` – Joe Stefanelli Feb 03 '11 at 22:49
  • 1
    @Jeff: Joe Stefanelli is giving you a way to solve your problem without cursors or looping. He's selecting from a system catalog, which is a view that you can query. Unless the question changes, I'd say this is an answer. Does it not give what you want? – Jeremy Pridemore Feb 04 '11 at 02:06
  • +1. This may not answer the OP's expectations, but certainly answers the question. – Andriy M Feb 04 '11 at 06:26