Why aren’t all the indexes created?

SQL Server Administration
1

Hi forum. I’m new in PowerShell but i tried to find the answer so hard for my own. I’m trying to create all the indexes for 4k tables, the problem is: i can get a lot of indexes in a sql file but there’s some exceptions that i cannot get and i don’t know why. I thought $ScriptIndex.Options.Indexes = $True should be enough to get all indexes in powershell. The section of the code in powershell:

foreach ($table in $TBLs | where {!($_.IsSystemObject)}  ){
            foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
            $ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
            $ScriptIndex.Options = $CreationScriptOptions
            $ScriptIndex.Options.DriAll = $True
            $ScriptIndex.Options.Indexes = $True
            $Bien = $Index.GetType().Name
            $TypeFolder= "$($Bien)_ParaVerificarPKs"
            Write-Host "$TypeFolder"
            $ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"        
            $ScriptIndex.Script($Index) 
            }
        }

I compared some tables to see an specific reason about why and i didn’t notice anything, some of the tables with no indexes have a FK as well but other tables have FK too, Other thing that i thought was the cluster or non-cluster, but, same case, some tables with no indexes are non-cluster and cluster. I hope someone can help me, i’m stuck for weeks. Sorry for the grammar and well, i posted it here too because maybe you know a reason about it, thanks!.

2

Can you use dbatools?

3

Thanks for read me, and yes, i'll try it, i mean, it works with a lot of tables but yeah, thank you, i'll reply you as soon as i get a result!

4

Well, also i cannot download a lot of stuffs at the company... Maybe a native solution? Oh, btw, if a create the indexes in SQL with the option: Include Index as...>Create To...>Any option. It works

5

The reason I suggested it was that I had already posted a script that was 90-95% of what you were asking for. From what you posted, I was unable to get it working on my end. For instance, how is the $TBLs variable populated?

It's possible your $TBLs variable is either empty, or it doesn't contain Table objects such that it has an Indexes collection. If you could post the entire script, that might help explain it.

One suggestion is to modify the for loop to just print out the name of each table. From there, try printing out the name of each index for each table. That would at least demonstrate that the loops are working correctly. You can add back in each line for generating the script.

I'm not well-versed in PowerShell for SQL Server beyond what's offered by dbatools. For instance, I've seen the Microsoft.SqlServer.Management.Smo references before, but it's been a long while since I've used them. dbatools makes many of these tasks much easier.

6

Hi again Robert, well i gotta mention the script was created over 10 years ago, and i usually don't develop for PowerShell or SQL, the function is:

function global:Script-DBObjectsIntoFoldersAIndexes([string]$server, [string]$dbname, [string]$user, [string]$pass, [string]$Path, [string]$bcp_path, [boolean]$Giro, [string]$prefijoEmp, [System.Data.DataTable]$TblO, [System.Data.DataTable]$TblIndx){

$SPs = @()
$TBLs = @()
$USRs = @()
$TBLs2 = @()

$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
#This sets the connection to mixed-mode authentication 
$SMOserver.ConnectionContext.LoginSecure=$false; 
#This sets the login name 
$SMOserver.ConnectionContext.set_Login($user); 
#This sets the password 
$SMOserver.ConnectionContext.set_Password($pass); 
$db = $SMOserver.databases[$dbname.trim()]

$DateFolder = get-date -format yyyyMMdd
$NewFolder = $($prefijoEmp)+$($dbname.trim())+"_"+$($DateFolder.trim())+"_"+$($letra) #ALGA 04/05/16 Se agrego la letra que identifica si es Operativo(O) o Estadístico(E)
$SavePath = $Path + $NewFolder


$SPs += $db.StoredProcedures | where {!($_.IsSystemObject)}
#Cargar en el arreglo solo las tablas operativas o estadísticas ALGA 05/05/16
#RJSV 16/08/2017 -- Se separan las tablas por bases de datos que son de giro porque el objeto $db solo puede obtener tablas de dbo
if($Giro){
    Foreach ($tbs in $tblO.Rows){
        #$TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
        $table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tbs[0], 'Supervisor_giro')
        $table.Initialize()
        $table.Refresh()
        $TBLs += $table
    }
}else{
    Foreach ($tbs in $tblO.Rows){
        $TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
    }
}

#Carga todas las tablas para crear todos los ínidices en la carpeta de O
if($Giro){
    Foreach ($tbs in $TblIndx.Rows){
        #$TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
        $table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tbs[0], 'Supervisor_giro')
        $table.Initialize()
        $table.Refresh()
        $TBLs2 += $table
    }
}else{
    Foreach ($tbs in $TblIndx.Rows){
        $TBLs2 += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
    }
}

$USRs += $db.Users | where {!($_.IsSystemObject)}

if(Test-Path "$SavePath"){
	Write-Host "Verify directory $SavePath" | Out-File -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force
}else{
	new-item -type directory -name "$NewFolder"-path "$Path"
}

if(Test-Path "$($SavePath.trim())\$($dbname.trim())_Log.txt"){
	$Msg =  "Inicia respaldo"  
	Write-Host (Get-Date).ToString() " $($Msg)"  
	Out-File  -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}else{
	new-item -type file -name "$($dbname.trim())_Log.txt" -path "$($SavePath.trim())"
	$Msg =  "Inicia respaldo"  
	Write-Host (Get-Date).ToString() " $($Msg)"  
	Out-File  -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}


$finalPath = "$($SavePath.trim())"
#Declaramos las opciones generales para hacer el script
	write-host "configure script options"
	$CreationScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
	$CreationScriptOptions.AppendToFile = $True
	$CreationScriptOptions.AllowSystemObjects = $false
	$CreationScriptOptions.ToFileOnly = $true
	$CreationScriptOptions.IncludeIfNotExists = $true
	$CreationScriptOptions.IncludeDatabaseRoleMemberships = $True
	$CreationScriptOptions.IncludeDatabaseContext = $True

#En esta parte se ah decidido crear por separado los SP, TRIGGERS, INDICES, TABLAS y BD.
	<# Se crea el script para las tablas , usuarios, roles etc. SOLO PARA RESPALDOS OPERATIVOS#>

If ($contador -eq 0){ #INICIO DEL IF 1 --Solo se hará para los respaldos operativos
$Tcount = 0

Do{
	try{
        #$query= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptTransfer = new-object ('Microsoft.SqlServer.Management.Smo.Transfer') $db
       
		$scriptTransfer.options=$CreationScriptOptions
		$scriptTransfer.options.Permissions = $true 
		$scriptTransfer.options.FileName = "$finalPath\DBobjectsScript.SQL"
		$scriptTransfer.CopyAllDefaults=$False
		$scriptTransfer.CopyAllStoredProcedures=$False
		$scriptTransfer.CopyAllDatabaseTriggers =$False
		$scriptTransfer.CopyAllObjects =$False 
        $scriptTransfer.CopyAllTables=$True
		$scriptTransfer.CopyAllViews=$true
		$scriptTransfer.CopyAllRoles=$true
		$scriptTransfer.CopyAllRules=$true
		$scriptTransfer.CopyAllSchemas=$true        
		#RJSV 16/08/2017 -- Se separan del respaldo de DBobjects principal
        #$scriptTransfer.CopyAllUsers=$true
		#$scriptTransfer.CopyAllLogins=$true
        $scriptTransfer.CopyAllUserDefinedFunctions=$true #ALGA 05/05/16 Se agregó para copiar las funciones de la BD
		#RJSV 16/08/2017 -- Se agregan opciones para copiar tipos de datos y tipos de tablas definidas por usuarios
        $scriptTransfer.CopyAllUserDefinedDataTypes=$true
        $scriptTransfer.CopyAllUserDefinedTableTypes=$true
        
        #RJSV 25/08/2017 -- Solucion tmp indices de giro no se pueden generar en Index.SQL ni los triggers
        if($Giro){
           $scriptTransfer.options.Indexes = $True
           $scriptTransfer.options.DriAll = $True
           $scriptTransfer.Options.Triggers= $True 
        }
        
        $scriptTransfer.DestinationDatabase = ' '
		$scriptTransfer.ScriptTransfer()
		
		if(Test-Path "$finalPath\DBobjectsScript.SQL"){
			$Msg = "Done create DB.Objects script" 
			write-host "Done create DB.Objects script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script creat DB.  $_ .Trying again..." 
			write-host "error while creating script creat DB.  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB.  $_" 
		Write-Error "error while creating script creat DB.  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DBobjectsScript.SQL"))

#RJSV 28/08/2017 - Se separan los permisos de los usuarios por base de datos
$Tcount = 0
Do{
	try{
		$Msg = "Scripting Permissions..." 
		write-host "Scripting Permissions..." 
        
        if($Giro){
        Exportar-Permisos -SqlInstance $server -uDecrypt $user -pDecrypt $pass -FilePath "$finalPath\DBpermissionsScript.SQL" -Database $dbname -Giro $True
        }else{
        Exportar-Permisos -SqlInstance $server -uDecrypt $user -pDecrypt $pass -FilePath "$finalPath\DBpermissionsScript.SQL" -Database $dbname
        }
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
	
		
		if(Test-Path "$finalPath\DBpermissionsScript.SQL"){
			$Msg = "Done create DBpermissionsScript script" 
			write-host "Done create DBpermissionsScript script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script creat DB.  $_ .Trying again..." 
			write-host "error while creating script creat DB.  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB.  $_" 
		Write-Error "error while creating script creat DB.  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DBpermissionsScript.SQL"))


	<# Se crea el script DROP SOLO EN RESPALDOS OPERATIVOS#>

$Tcount = 0
Do {
	try{
		$scriptrDBdrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDBdrop.Options = $CreationScriptOptions
		$scriptrDBdrop.Options.Permissions = $true 
		$scriptrDBdrop.Options.ScriptDrops = $True
		$scriptrDBdrop.Options.FileName = "$finalPath\$($dbname.trim())_drop.SQL"
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		<#Crea script para eliminar la base de datos anterior#>
		$scriptrDBdrop.Script($db)
		if(Test-Path "$finalPath\$($dbname.trim())_drop.SQL"){
			$Msg = "Done drop DB script" 
			write-host "Done drop DB script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script drop DB $_ . Trying again..." 
			write-host "error while creating script drop DB $_ . Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg =   "error while creating script drop DB $_" 
		Write-Error "error while creating script drop DB $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\$($dbname.trim())_drop.SQL"))
	
	<# Se crea el script Crear base de datos (no se utiliza realmente) SOLO RESPALDOS OPERATIVOS #>
$Tcount = 0
Do{
	try{
		$scriptrDB = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDB.Options = $CreationScriptOptions
		$scriptrDB.Options.Permissions = $true 
		$scriptrDB.Options.ScriptDrops = $False
		$scriptrDB.Options.DdlBodyOnly = $False
		$scriptrDB.Options.FileName = "$finalPath\$($dbname.trim())_perm.SQL"

		$Msg =  "Scripting..."
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg

		$scriptrDB.Script($db)
		
		if(Test-Path "$finalPath\$($dbname.trim())_perm.SQL"){
			$Msg =  "Done create DB script" 
			write-host "Done create DB script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg =  "error while creating script creat DB. $_ . Trying again..." 
			write-host "error while creating script creat DB. $_ . Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB. $_" 
		Write-Error "error while creating script creat DB. $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}While($contador -eq 1)
#While(-Not (Test-Path "$finalPath\$($dbname.trim())_perm.SQL"))

	<# Se crea el script para los SP SOLO RESPALDOS OPERATIVOS#>
$Tcount = 0	
Do{
	try{
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptrDBSP = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDBSP.Options = $CreationScriptOptions
		$scriptrDBSP.Options.Permissions = $True
		$scriptrDBSP.options.FileName = "$finalPath\DB_SP.SQL"
		
		if(($SPs.Length -ne 0) -and ($SPs -ne '')){
			foreach($SP in $SPs | where {!($_.IsSystemObject)}){
				$scriptrDBSP.Script($SP) | Out-null
			}
		}
		if(Test-Path "$finalPath\DB_SP.SQL"){
			$Msg =  "Done create SP's script" 
			write-host "Done create SP's script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}else{
			$Msg =  "There are no Stored Procedures to generate the script." 
			Write-Host "There are no Stored Procedures to generate the script." -Fore Green
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		$Msg = "error while creating script SP. $_" 
		Write-Error "error while creating script SP. $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DB_SP.SQL"))
}#FIN del IF 1

	<# Se generan los .txt del BCP con la informacion de las tablas SE GENERAN POR TABLA #>
try{
	$Msg = (Get-Date).ToString()+ " Starting bcp backup"
	write-host "Starting bcp backup" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg

   ##AQUI SE GENERAN LOS TXTS DE LAS TABLAS## -- ALGA
	Foreach ($tb in $tblO.Rows){
		if($Giro  -eq $true){
	            	$arguments = "$($db.Name).Supervisor_Giro.$($tb[0])",
	                        'out',"$($finalPath)\$($tb[0]).txt",
	                        '-t"²"',
	                        '-r"{CR}{LF}"',
			        '-o',"$($finalPath)\BCP_log.txt",
	                        '-q',
	                        '-c',
		     		'-k',
	                        '-U',"$($user)",
	                        '-P',"$($pass)",
	                        '-S',"$($server)"
		}else{
	            	$arguments = "$($db.Name).dbo.$($tb[0])",
	                        'out',"$($finalPath)\$($tb[0]).txt",
	                        '-t"²"',
	                        '-r"{CR}{LF}"',
			        '-o',"$($finalPath)\BCP_log.txt",
	                        '-q',
	                        '-c',
		     		'-k',
	                        '-U',"$($user)",
	                        '-P',"$($pass)",
	                        '-S',"$($server)"
		}

		& $bcp_path $arguments | Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force

		$Msg = "Finish $($tb[0])" 
		write-host "Finish $($tb[0])" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
    }

	$Msg = (Get-Date).ToString() +"Finish bcp backup"
	write-host "Finish bcp backup" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}Catch [system.exception]{
	$Msg = "Error during the bcp backup process $_" 
	Write-Error "Error during the bcp backup process $_" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
    break;
}

#>


	<# Se crea el script para los indices SE GENERAN POR TABLA#>
If(-Not ($Giro)){
$Tcount = 0
Do{
	try{      
		$Msg = "Scripting Indexes..." 
		write-host "Scripting Indexes..." 
        write-host "$($finalPath.trim())\$($dbname.trim())_Log.txt"
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        
        
		foreach ($table in $TBLs2 | where {!($_.IsSystemObject)}  ){
			foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
			$ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
			$ScriptIndex.Options = $CreationScriptOptions
			$ScriptIndex.Options.DriAll = $True
			$ScriptIndex.Options.Indexes = $True
			$TypeFolder=$Index.GetType().Name
			$ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"		
			$ScriptIndex.Script($Index) 
			}
		}
        
		if(Test-Path "$finalPath\$TypeFolder.SQL"){
			$Msg = "Done create indexes script" 
			write-host "Done create indexes script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			
            $Msg = "error while creating script creat DB  $_ .Trying again..." 
			write-host "error while creating script creat DB  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		
        $Msg = "error while creating script creat DB  $_" 
		Write-Error "error while creating script creat DB  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\$TypeFolder.SQL"))


$Tcount = 0

    <#Indexes solo Operativos en IndexParaVerificar.sql    #>
    <#
Do{
	try{

        
		$Msg = "Scripting Indexes..." 
		write-host "Scripting Indexes..." 
        write-host "$($finalPath.trim())\$($dbname.trim())_Log.txt"
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        
        
		foreach ($table in $TBLs | where {!($_.IsSystemObject)}  ){
			foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
			$ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
			$ScriptIndex.Options = $CreationScriptOptions
			$ScriptIndex.Options.DriAll = $True
			$ScriptIndex.Options.Indexes = $True
            $Bien = $Index.GetType().Name
			$TypeFolder= "$($Bien)_ParaVerificarPKs"
            Write-Host "$TypeFolder"
			$ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"		
			$ScriptIndex.Script($Index) 
			}
		}
        
		if(Test-Path "$finalPath\$TypeFolder.SQL"){
			$Msg = "Done create indexes script" 
			write-host "Done create indexes script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			
            $Msg = "error while creating script creat DB  $_ .Trying again..." 
			write-host "error while creating script creat DB  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		
        $Msg = "error while creating script creat DB  $_" 
		Write-Error "error while creating script creat DB  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While($contador -eq 1)
#>


	<# Se crea el script para los triggers SE GENERAN POR TABLA #>

$Tcount = 0
Do{
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptTable = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
		$scriptTable.Options = $CreationScriptOptions
		$scriptTable.options.Permissions = $True 
		$scriptTable.Options.Triggers= $True 
		$scriptTable.Options.IncludeDatabaseContext = $True
		$scriptTable.Options.FileName = "$finalPath\DB_Triggers.SQL"
       
		If (($TBLs2.Length -ne 0) -and ($TBLs2 -ne '')){
			foreach($TBL in $TBLs2 | where {!($_.IsSystemObject)}){#Aquí truena con DWH
				foreach($Triggers in $TBL.Triggers){
					$scriptTable.Script($Triggers)
				}
			}
		}
		
		if(Test-Path "$finalPath\DB_Triggers.SQL"){
			$Msg = "Done create DB Triggers script" 
			write-host "Done create DB Triggers script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "There are no Triggers to generate the script." 
			write-host "There are no Triggers to generate the script."  
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
}
While(-Not (Test-Path "$finalPath\DB_Triggers.SQL"))
}

} #This completes the function

So, I think the loop works because it brings the backup for all the tables with no exception, and well, the functions is called in:

$queryFull= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'

#Llama a las tablas por su tipo
$queryEsp= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where ' + $versionRespaldo + ' and DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'



$cmd2=new-object system.Data.SqlClient.SqlCommand($queryEsp,$cnn)
$cmd2.CommandTimeout=120
$cnn.Open()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $cmd2
$tables = New-Object System.Data.DataSet
$SqlAdapter.Fill($tables)
$res=New-Object System.Data.DataTable
$res=$tables.Tables[0]

$cmd3=new-object system.Data.SqlClient.SqlCommand($queryFull,$cnn)
$cmd3.CommandTimeout=120
$SqlAdapter3 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter3.SelectCommand = $cmd3
$tables3 = New-Object System.Data.DataSet
$SqlAdapter3.Fill($tables3)
$res3=New-Object System.Data.DataTable
$res3=$tables3.Tables[0]

$cnn.Close()


    #Aquí llama la función que crea todos los objetos, enviando los parámetros de la BD actual-- ALGA 
    If ($contador -eq 0){
        Script-DBObjectsIntoFoldersAIndexes $server $BDNOMBRE $udecrypted $pdecrypted $path $bcp_path $Giro $prefijoEmp $res $res3
	}Else{
        Script-DBObjectsIntoFolders $server $BDNOMBRE $udecrypted $pdecrypted $path $bcp_path $Giro $prefijoEmp $res
    }

And thanks again for all! Maybe with this could be more understandable.

7

I got the following to work:

$server='.\HOBBES'  # local default instance
$dbname='AdventureWorks'  # test DB
$user="sa"  # test user
$pass="myStrongPassword"  # test password

$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
#This sets the connection to mixed-mode authentication
$SMOserver.ConnectionContext.LoginSecure=$false; 
#This sets the login name 
$SMOserver.ConnectionContext.set_Login($user); 
#This sets the password 
$SMOserver.ConnectionContext.set_Password($pass);
$db = $SMOserver.databases[$dbname.trim()]

# the following is modified from your original. 
# I removed lines from there to get a minimum working example
# loop works correctly and scripts to file
# your original should work as-is, double check AppendToFile is set to $true

foreach ($table in $db.Tables ){  # changed to ignore $TBLs2 variable
	foreach ($Index in $table.Indexes){
	$ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $ScriptIndex.Options.AppendToFile = $True  # maybe this needs to be set
	$ScriptIndex.Options.Indexes = $True
    $ScriptIndex.Options.FileName = "C:\Backups\Indexes.sql"
	$ScriptIndex.Script($Index) 
	}
}

I couldn't get the original to work on my machine, as the function parameters were too complicated.

The function also seemed to be more elaborate than necessary. There were multiple checks for whether a table was a system table, they were redundant or probably unnecessary. The example I tested didn't script any indexes for system tables.

8

Hi again and thanks for the help, i'll test it asap, and i'll share you the result, thanks again Robert!