1

I have a collection of .sql files containing ddl for various database objects.

For example:

  1. User.sql
  2. Group.sql
  3. GroupUser.sql

Is there a way I can use a stored procedure to easily/elegantly load/execute these files in sequence? For example, GroupUser.sql depends on the existence of User and Group, so I need to execute the .sql files in the order listed above.

I know I could concatenate the contents of all of the .sql scripts above into a stored procedure, but I would prefer to take a more modular approach. I could also put each script into its own stored procedure but I'd rather not clutter the stored procedure collection in my app database with DDL setup scripts.

jarlh
  • 42,561
  • 8
  • 45
  • 63
user9393635
  • 1,369
  • 4
  • 13
  • 32
  • below stack answer explains this. https://stackoverflow.com/questions/2583517/run-all-sql-files-in-a-directory – Tukaram Bhosale Mar 21 '18 at 18:57
  • 1
    Strictly speaking, SQL Server doesn't even know what a `.sql` file is, much less what to do with it. The server is only aware of the commands sent to it. Client tools are much more useful for this. – Alejandro Mar 21 '18 at 20:04

1 Answers1

0

From SSMS, go to the "Query" menu, and select "SQLCMD Mode". Once there, you can run commands like this. I script out stuff like this all the time.

use test
go

:r "D:\SomeDataDirectory\SomeSQLFile.sql"

EDIT: Didn't see you wanted to do this within a stored procedure. That's a bit of a dicey proposition. Assuming you have permissions to execute it, you could put the same SQLCMD code in calls to xp_cmdshell, but in many circumstances that won't be an option for you unless you've got admin-like permissions on the server.

Xedni
  • 3,662
  • 2
  • 16
  • 27