Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.