Generate sql in the instance

Question

Post reply

Generate sql in the instance

vpmaciel

Old Hand

Points: 367
More actions
October 15, 2023 at 1:44 am

#4308259

I need to generate SQL code that, in the instance, creates all the objects for each of the databases and generates an SQL output code.
I need to do this via script, it can be PowerShell or T-SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

And what have you searched on said topic?

A simple search on this topic will yield a plethora of results.

If you have issues with a specific method then by all means a more targeted question will be happy to assist.

Check out DBATools.io also

vpmaciel Old Hand Points: 367 More actions · Answer 2

I have a code generated by other person, but it does not work. The code follows below:

vpmaciel Old Hand Points: 367 More actions · Answer 3

USE [msdb]
GO

/****** Object: Job [DBA - Gerar Script de Criação de Todos os Bancos e Objetos] Script Date: 15/10/2023 07:04:19 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 15/10/2023 07:04:19 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Gerar Script de Criação de Todos os Bancos e Objetos', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=2, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'Esse job tem como objetivo gerar o script de todos os bancos com todos os objetos envolvidos', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', 
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Gerar script de todos os bancos] Script Date: 15/10/2023 07:04:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Gerar script de todos os bancos', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'PowerShell', 
@command=N'$date_ = (date -f yyyyMMdd)
$ServerName = "." #If you have a named instance, you should put the name. 
$path = "c:\SQL_Server\Backup\Objects\"+"$date_"

[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')
$serverInstance = New-Object (''Microsoft.SqlServer.Management.Smo.Server'') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. 
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object (''Microsoft.SqlServer.Management.Smo.ScriptingOptions'')


$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
$dbname = "$db".replace("[","").replace("]","")
$dbpath = "$path"+ "\"+"$dbname" + "\"
if ( !(Test-Path $dbpath))
{$null=new-item -type directory -name "$dbname"-path "$path"}

foreach ($Type in $IncludeTypes)
{
$objpath = "$dbpath" + "$Type" + "\"
if ( !(Test-Path $objpath))
{$null=new-item -type directory -name "$Type"-path "$dbpath"}
foreach ($objs in $db.$Type)
{
If ($ExcludeSchemas -notcontains $objs.Schema ) 
{
$ObjName = "$objs".replace("[","").replace("]","") 
$OutFile = "$objpath" + "$ObjName" + ".sql"
$objs.Script($so)+"GO" | out-File $OutFile
}
}
} 
}', 
@database_name=N'master', 
@output_file_name=N'C:\Temp\Gerar script de todos os bancos.log', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

frederico_fonseca SSCoach Points: 16155 More actions · Answer 4

why do you say it does not work?

SMO, and that particular script (with a few changes required on the scriptingoptions block as it its missing significant items) is one of the ways you can do it.

This reply was modified 1 year, 1 month ago by frederico_fonseca.

Emperor100 SSCrazy Points: 2173 More actions · Answer 5

I was able to create this job in my dev sql environment and ran it successfully. No issues et all.

Make sure the sql server service account has access to all the folders

=======================================================================