Script out all SQL objects under particular schema

Question

Is there any way I can script out all the SQL Server objects (tables, SP, functions etc) under a schema?

In our database we have a table containing name of all the schemas and there are more than 500 schema. Some of them are for dev and some are prod. I need to script out all the objects under dev schema and create a new database.

For this purpose you should use PowerShell environment with ScriptingOptions object — Oleksandr Fedorenko
– Oleksandr Fedorenko, Commented Sep 10, 2014 at 7:10
It can be useful to use two different databases (one for dev and one for the "production" version). I'm using branching with databases, and also the deploy processes (additionally the continuous integration process) is very clear and comfortable. Then you can use a simple script, SSMS or else some third party tools designed for comparing schema. — Alessandro Alpi
– Alessandro Alpi, Commented Sep 11, 2014 at 0:05

V.Jokinen · Accepted Answer · 2017-08-28 06:27:05Z

ApexSQL Script is the tool which can be very helpful in this situation. It is the tool which creates SQL scripts for database objects, and it can script all objects into one script or all objects separately.

For this situation here is what you should do:

Select server and the database which you want to script and load them.
Go to the View tab and click the “Object filter” button, then select the “Edit filter” button:

In the Filter editor for all objects select the “Include if:” and “Click here to add filter criteria”:

Select the “Schema”, “Equals” and Enter the desired schema name, then click OK:

Click on the Home tab, check all objects and Click the “Script” button:

In the third step of the Synchronization wizard, under the Script file tab, select if you want to create one script for all objects or for each object individually from the Granularity drop down menu:

In the last step of the Script wizard click the Create button and check out the results – you will have the script which can be executed in the SQL Server Management Studio.

Amir Razmjou · Accepted Answer · 2017-04-04 19:14:49Z

This is PowerShell answer to your problem.

$Server= 'SERVER_NAME'
$Database= 'DATABASE_NAME'
$SmoScriptPath = 'SCRIPT_OUTPUT.SQL'  
$Schemas = @("dlo", "deo")    # include objects that fall under this schema set
$ObjectTypes = @("StoredProcedures", "Views", "Tables")    #object types to be included 


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$SmoServer = new-object "Microsoft.SqlServer.Management.SMO.Server" $Server
$SmoServer.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$SmoDb = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$SmoDb = $SmoServer.Databases[$Database]
$SmoScr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$SmoScr.Server = $SmoServer
$SmoScr.Options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$SmoScr.Options.AllowSystemObjects = $false
$SmoScr.Options.IncludeDatabaseContext = $true
$SmoScr.Options.IncludeIfNotExists = $false
$SmoScr.Options.ClusteredIndexes = $true
$SmoScr.Options.Default = $true
$SmoScr.Options.DriAll = $true
$SmoScr.Options.Indexes = $true
$SmoScr.Options.NonClusteredIndexes = $true
$SmoScr.Options.IncludeHeaders = $false
$SmoScr.Options.ToFileOnly = $true
$SmoScr.Options.AppendToFile = $true
$SmoScr.Options.ScriptDrops = $false 
$SmoScr.Options.Triggers = $true
$SmoScr.Options.ExtendedProperties = $true
$SmoScr.Options.FileName = $SmoScriptPath
New-Item $SmoScr.Options.FileName -type file -force | Out-Null


Foreach ($ObjectType in $ObjectTypes) {
    $Objects = $SmoDb.$ObjectType | where {$_.IsSystemObject -eq $false -and $Schemas.Contains($_.Schema)}

    Foreach ($Object in $Objects | where {$_ -ne $null})
    {        
       $SmoScr.Script($Object)        
    }
}

Is there a way to overwrite the schema names when generating the scripts? For example, I have a few Schemas [dbo, dwo, etc...] and I would like to change them all to "xxxx" when generating the script. I guess I could just regex it, but that doesn't sound like the best solution.

pritamS · Accepted Answer · 2014-09-11 05:55:36Z

2

Thanks guys for your reply. I have solved this by generating all the scripts through SSMS and then created a schema only database. Than I dropped all the tables, views SP, functions etc those are not part of the schema I do not need.

It took me around 20 mins to do that. But after all the work is done.

answered Sep 11, 2014 at 5:55

pritamS

1372 silver badges11 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

If you are looking for a solution for getting these objects scripted on a schedule and checked into a GIT Repo Automatically.

Check out the following project I have shared: https://github.com/Drazzing/mssqlobjectstogit

What you get:

Changes to MS SQL Objects over time in [GIT]
Report that shows who Added / Deleted / Changed the objects over time [CSV]

Report Example:

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 9, 2019 at 4:51

Draz

1882 silver badges5 bronze badges

Comments

Amir Keshavarz · Accepted Answer · 2014-09-10 04:28:28Z

0

You can use Redgate SQL Compare or use management studio generate script feature.

answered Sep 10, 2014 at 4:28

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

2 Comments

pritamS Over a year ago

But it will script out all the objects. I need to script out objects which belongs to some particular schema.

Amir Keshavarz Over a year ago

Use sys.sql_module too.

Collectives™ on Stack Overflow

Script out all SQL objects under particular schema

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related