Skip to content

Instantly share code, notes, and snippets.

@rahulpnath
Last active November 29, 2019 19:48
Show Gist options
  • Save rahulpnath/ffb2d67cf094d682c394faf11477323d to your computer and use it in GitHub Desktop.
Save rahulpnath/ffb2d67cf094d682c394faf11477323d to your computer and use it in GitHub Desktop.
GIven an old tableName and newTableName and database details, the script generates a SQL script file to drop create all Index, Constraints. This script is useful if you use convention based naming for tables and the associated Index and Keys and want them all renamed.
#I @"C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\";;
#I @"C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies";;
#r "Microsoft.SqlServer.Smo.dll";;
#r "Microsoft.SqlServer.ConnectionInfo.dll";;
#r "Microsoft.SqlServer.Management.Sdk.Sfc.dll";;
open System.IO
open Microsoft.SqlServer.Management.Smo
let renameTableScript oldTableName newTableName = sprintf "EXEC sp_rename '%s', '%s'" oldTableName newTableName
let generateScripts scriptingOpitons (table:Table) =
let indexes = table.Indexes |> Seq.cast |> Seq.collect (fun (index:Index) -> (index.Script scriptingOpitons |> Seq.cast<string>))
let fks = table.ForeignKeys |> Seq.cast |> Seq.collect (fun (fk:ForeignKey) -> fk.Script scriptingOpitons |> Seq.cast<string>)
let all = Seq.concat [fks; indexes]
Seq.toList all
let generateDropScripts (table:Table) =
let scriptingOpitons = ScriptingOptions(ScriptDrops = true, DriAll = true, DriAllKeys = true, DriPrimaryKey = true, SchemaQualify = false)
generateScripts scriptingOpitons table
let generateCreateScripts (table:Table) =
let scriptingOpitons = ScriptingOptions( DriAll = true, DriAllKeys = true, DriPrimaryKey = true, SchemaQualify = false)
generateScripts scriptingOpitons table
let generateRenameScripts (serverName:string) (databaseName:string) (oldTableName:string) newTableName =
let server = Server(serverName)
let db = server.Databases.[databaseName]
let oldTable = db.Tables |> Seq.cast |> Seq.tryFind (fun (t:Table) -> t.Name = oldTableName)
let dropScripts = generateDropScripts oldTable.Value
let renameScript = [renameTableScript oldTableName newTableName]
let createScripts = generateCreateScripts oldTable.Value |> List.map (fun s -> s.Replace(oldTableName, newTableName) )
let script = dropScripts @ renameScript @ createScripts
script
let script = generateRenameScripts "ServerName" "DatabaseName" "OldTableName" "NewTableName"
File.WriteAllLines (@"C:\Work\Scripts\test.sql", script) |> ignore
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment