Sql Executor

cnnArtricenterProduccion:
Server=192.168.4.2;Database=Artricenter_Produccion;User Id=sisco1;Password=L3f5rm5$$001;Connection Timeout=360;TrustServerCertificate=true;Encrypt=false

cnnAsistencias:
Server=192.168.1.5;Database=Asistencias;User Id=sisco2Asistencias;Password=L3f5rm5$$001;Connection Timeout=360;TrustServerCertificate=true;Encrypt=false

cnnAsokam:
Server=192.168.4.2;Database=Asokam;User Id=coreapi;Password=L4_CL4VE_S3cReta_Y_sUp3r__SEGUR4_123!;Connection Timeout=360;TrustServerCertificate=true;Encrypt=false

cnnLefarma:
Server=192.168.4.2;Database=Lefarma;User Id=coreapi;Password=L4_CL4VE_S3cReta_Y_sUp3r__SEGUR4_123!;Connection Timeout=360;TrustServerCertificate=true;Encrypt=false

Workflow

1. User asks to run SQL query
2. Ask: "¿En qué base de datos quieres ejecutar la consulta?" (show options above)
3. Wait for user response
4. Execute query on selected database
5. Show results

Execute Query

Using sqlcmd

# Artricenter Produccion
sqlcmd -S 192.168.4.2 -d Artricenter_Produccion -U sisco1 -P "L3f5rm5$$001" -Q "SELECT * FROM tabla"

# Asistencias
sqlcmd -S 192.168.1.5 -d Asistencias -U sisco2Asistencias -P "L3f5rm5$$001" -Q "SELECT * FROM tabla"

# Asokam
sqlcmd -S 192.168.4.2 -d Asokam -U coreapi -P "L4_CL4VE_S3cReta_Y_sUp3r__SEGUR4_123!" -Q "SELECT * FROM tabla"

Using PowerShell Script

.\execute-sql.ps1 -Database Artricenter_Produccion -Query "SELECT * FROM tabla"

Common Queries

Check Database State

-- List all tables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- List all views
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

-- Check table schema
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NombreTabla'

Check Foreign Keys

-- Get all foreign keys
SELECT
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id

Best Practices

1. ALWAYS ask which database first
2. Use transactions for UPDATE/DELETE
3. Check query execution plans for performance
4. Backup data before running destructive queries
5. Use parameterized queries to prevent SQL injection

Utility Scripts

check_tables.ps1 - Check for LDAP Auth Tables

Checks for domain configuration and identity tables in Asokam database.

$connectionString = "Server=192.168.4.2;Database=Asokam;User Id=coreapi;Password=L4_CL4VE_S3cRta_Y_sUp3r__SEGUR4_123!;Connection Timeout=30;TrustServerCertificate=true;Encrypt=false"
$query = @"
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Dominio%' OR TABLE_NAME = 'Usuarios' OR TABLE_NAME = 'Roles'
"@

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataset = New-Object System.Data.DataSet

try {
    $connection.Open()
    $adapter.Fill($dataset)
    $connection.Close()
    $dataset.Tables[0] | Format-Table -AutoSize
}
catch {
    Write-Host "Error: $_" -ForegroundColor Red
}

test_view.ps1 - Test vwDirectorioActivo View

Tests the Active Directory view in Lefarma database.

$connectionString = "Server=192.168.4.2;Database=Lefarma;User Id=coreapi;Password=L4_CL4VE_S3cRta_Y_sUp3r__SEGUR4_123!;Connection Timeout=30;TrustServerCertificate=true;Encrypt=false"
$query = "SELECT TOP 5 * FROM vwDirectorioActivo"

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataset = New-Object System.Data.DataSet

try {
    $connection.Open()
    $adapter.Fill($dataset)
    $connection.Close()
    $dataset.Tables[0] | Format-Table -AutoSize
}
catch {
    Write-Host "Error: $_" -ForegroundColor Red
}