How can I make a SQL server query loop through files?

I need to create several databases at once. I have the .BAK file for the dbs and I would like to loop through those files then have SQL create the databases based on the name of the .BAK.

I already have a query to create a database but I seem to be having trouble with the loop.

How would I make SQL server check my .BAK files and cre开发者_JAVA技巧ate DBs accordingly?

Thanks!

---

I would take a different approach with this and leave the actual looping done in a small program. Have the file system handle the files and you can issue a procedure (stored procedure) to do the backup directly from your application.

I know it's not the answer you are after just giving you additional ideas...

---

I would use an external tool to do something like this.

Use client side scripting to browse the directory (Powershell perhaps?) and then pass the bak file names to the SQL commandline to create the databases.

---

You can do this with xp_cmdshell, but it's not recommended for the reasons they list in the article.

http://msdn.microsoft.com/en-us/library/ms175046.aspx