SQL Split AWK Script

Note: The current RegEx’s are tailored around the following scripting options (see comments to change):

• “Include descriptive headers” = true  (this is the default after SSMS 2008 install) – located under:  SSMS > Tools > Options > SQL Server Object Explorer > Scripting > General scripting options)
• “Schema qualify object names” =  false (NOT the default) – under: … > Object scripting options

Download GAWK.exe for Windows: link1, link2

# example: gawk -f sqlsplit.awk file-to-split.sql

BEGIN {
  outfile = "erase_me.sql" #start off with a dummy file to get the ball rolling
  IGNORECASE = 1
}

END {
  #close off last file
  print "grant "grant" on "arr[1]" to publicngon" >>outfile
  close(outfile)
}

//***** Object:/ {
  #upon matcing the "object" comment, close off the previous output file
  print "grant "grant" on "arr[1]" to publicngon" >>outfile
  close(outfile)

  #start up the new one
  match($0, /[(.*)]/, arr) #change to something like /[dbo].[(.*)]/ if you want “Schema qualify object names” enabled
  outfile = arr[1]".sql"
  print "--$Author:$n--$Date:$n--$Modtime:$n--$History:$n" > outfile
}

/^(create) +(proc|function|view)/ {

  grant = "execute"
  if ($2 == "view") grant = "select"

  printf "if not exists(select 1 from sysobjects where name = '"arr[1]"')ntexec('create "$2" "arr[1] >>outfile

  # function is a little trickier because it could be a table or scalar return type requiring slightly different create function signature
  if ($2 == "function") {
 
    lines = ""
    while((getline line) >0) {
      lines = lines line"n"
      match(line, /returns/, a)
      if (a[0] != "returns") { continue }

      #debug: printf "line = %s, a[0] = %s, a[1] = %s, a[2] = %s, a[3] = %sn", line, a[0], a[1], a[2], a[3]

      match(line, /table/, a)
      if (a[0] == "table") {
        grant = "select"
        print "() returns table as return select 1 as one')" >>outfile }
      else print "() returns int begin return 0 end')" >>outfile
      break

    }
  }

  #proc/view
  else {
    print " as select 1 as one')" >>outfile
  }

  print "GO" >>outfile

  sub(/create/, "alter") #change the create to alter
  sub(/$/, lines) #tack back on the lines "eaten" to figure out whether function was tabular or scalar
}


{ 
  print  >>outfile
}