none
generate scripts RRS feed

  • Question

  • Hi,

    I have 850 Stored Procedures in database. I want to generate script for 150 stored procedures. 

    Generate Script option is time consuming when we select 150 of 850 sps.

    Is there any alternative solution to Generate Script option.



    Saturday, July 20, 2019 2:38 PM

All replies

  • Below is an example PowerShell script that scripts procs using SMO.Scripter you can customize for your environment and needs. I would expect this to run in seconds rather than minutes.

    [string]$serverName = "." [string]$databaseName = "Adventureworks" [string]$scriptRootPath = "C:/SqlScripts" try {

    Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies/Microsoft.SqlServer.Smo.dll" Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies/Microsoft.SqlServer.SmoExtended.dll" } catch { Write-Host "$($_.Exception.LoaderExceptions)" } # replace characters invalid in folder path with specified character Function Replace-InvalidPathCharacters($path, $replaceCharacer) { foreach($invalidChar in [System.IO.Path]::GetInvalidPathChars()) { $path = $path.Replace($invalidChar, $replaceCharacer) } return $path } # replace characters invalid in file name with specified character Function Replace-InvalidFileNameCharacters($fileName, $replaceCharacer) { foreach($invalidChar in [System.IO.Path]::GetInvalidFileNameChars()) { $fileName = $fileName.Replace($invalidChar, $replaceCharacer) } return $fileName } # create folder if it doesn't already exist Function Create-ScriptFolder($folderPath) { if (!(Test-Path -Path $folderPath)) { try { New-Item $folderPath -Type Directory | Out-Null } catch [System.Exception] { throw "Error creating '$folderPath'" } } } # Create script for specified object, creating a subfolder for the object type, if needed. Function Create-ScriptForObject($objectUrn, $parentFolderPath) { # Create UrnCollection with the provided Urn item $UrnCollection = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection $UrnCollection.Add($objectUrn.Urn) # get folder name for script file type and create folder if it doesn't yet exist $objectTypePath = "$parentFolderPath/$($objectUrn.DatabaseObjectTypes)" $objectTypePath = Replace-InvalidPathCharacters -path $objectTypePath -replaceCharacer "-" Create-ScriptFolder -folderPath $objectTypePath if([String]::IsNullOrWhiteSpace($objectUrn.Schema)) { # file name for non-schema-scoped objects $objectFileName = "$($objectUrn.Name).sql" } else { # file name for schema-scoped objects $objectFileName = "$($objectUrn.Schema).$($objectUrn.Name).sql" } # replace invalid file name characters with dashes $objectFileName = Replace-InvalidFileNameCharacters -fileName $objectFileName -replaceCharacer "-" # get fully-qualified file name $scripter.Options.Filename = "$objectTypePath/$objectFileName" # show error message if file already exists and continue if (Test-Path -Path $scripter.Options.Filename) { Write-Host "[warning]Script file '$($scripter.Options.FileName)' already exists and will be replaced. May be because folder is not empty or invalid file name characters that resulted in file name conflicts" -ForegroundColor Yellow } # script objects to file try { $scripter.Script($UrnCollection) } catch [System.Exception] { throw } } ############ ### MAIN ### ############ try { $timer = [System.Diagnostics.Stopwatch]::StartNew() # change path to location for your machine (https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects) Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies/Microsoft.SqlServer.Smo.dll" Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies/Microsoft.SqlServer.SmoExtended.dll" $srv = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName) if ($srv.ServerType -eq $null) { throw "Unable to connect to server '$serverName' " } $db = $srv.Databases[$databaseName] # create scripter object with the desired scripting options $scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) $scripter.Options.ToFileOnly = $true $scripter.Options.AllowSystemObjects = $false $scripter.Options.IncludeDatabaseContext = $true $scripter.Options.NoCommandTerminator = $false # include all object types to be scripted $objectTypeFlags = [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::StoredProcedure Write-Host "Scripting object types: $objectTypeFlags" Write-Host "Scripting objects for database $databaseName..." # create database root folder $databaseRootPath = Join-Path $ScriptRootPath $databaseName $databaseRootPath = Replace-InvalidPathCharacters -path $databaseRootPath -replaceCharacer "-" Create-ScriptFolder -folderPath $databaseRootPath # exclude specific objects we don't want to script $objectsToScript = $db.EnumObjects($objectTypeFlags) | Where-Object { $_.Schema -notin ("sys","information_schema","guest","db_owner","db_accessadmin","db_securityadmin","db_ddladmin","db_backupoperator","db_datareader","db_datawriter","db_denydatareader","db_denydatawriter") -and !($_.DatabaseObjectTypes -eq "StoredProcedure" -and $_.Name -like "sp_*diagram*") } # script each object to separate file for ($i = 0; $i -lt $objectsToScript.Count; ++$i) { Create-ScriptForObject -objectUrn $objectsToScript[$i] -parentFolderPath $databaseRootPath } Write-Host "Scripting completed. Duration $($timer.Elapsed)" } catch { throw }



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, July 20, 2019 3:29 PM
  • Hi KIRAN KUAMR,

    You can query INFORMATION_SCHEMA.ROUTINES, the ROUTINE_DEFINITION column contains body of stored procedures.

    SELECT ROUTINE_DEFINITION, * FROM INFORMATION_SCHEMA.ROUTINES;

    Sunday, July 21, 2019 2:29 AM
  • Or you could use a multi-platform command tool MSSQL-Scripter to write custom code for generating scripts of SQL Server objects.

    MSSQL-Scripter Tool and Examples to Generate Scripts for SQL Server Objects

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 22, 2019 6:52 AM
    Moderator
  • Hi KIRAN KUAMR,

    You can query INFORMATION_SCHEMA.ROUTINES, the ROUTINE_DEFINITION column contains body of stored procedures.

    SELECT ROUTINE_DEFINITION, * FROM INFORMATION_SCHEMA.ROUTINES;

    Should use this query:

     SELECT  P.name AS sp_name 
    	,  m.[definition] AS sp_text
    	,len( m.[definition])  
    FROM sys.procedures P  
    JOIN sys.sql_modules  m ON P.object_id = m.object_id

    Monday, July 22, 2019 1:54 PM
    Moderator
  • Or you could use a multi-platform command tool MSSQL-Scripter to write custom code for generating scripts of SQL Server objects.

    MSSQL-Scripter Tool and Examples to Generate Scripts for SQL Server Objects

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Will, hello. Thank  you for the answer.

    Is no the project dead to invest time in it?

    https://github.com/microsoft/mssql-scripter/issues/222


    Sergey Vdovin

    Wednesday, August 14, 2019 7:09 PM
  • A word of caution when using the definition from the catalog views for textual objects. The text will contain the original object name if the object was subsequently renamed instead of the current name. Scripting using SMO or mssql-scripter will transform the text to the actual object name if different.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, August 16, 2019 11:44 AM