Skip to content

Triggers and Statistics in SQL Server #2313

Open
@junior-paytech

Description

@junior-paytech

I am working on a project where database has both triggers and statistics and when I try to drop trigger and after that drop statistics I got an error that I don't have permission or statistcs does not exists.

I am using "sa" user and the same sql script works when running it on SQL Express or other DBMS.

Does anybody have any suggestion?

I also created a "Base migration" that extends AbstractMigration to implement the methods to check for statistics

<?php

declare(strict_types=1);

namespace App\Infrastructure\Database\Migrations;

use Phinx\Migration\AbstractMigration;

abstract class BaseMigration extends AbstractMigration
{
    public function getStatistics(string $tableName, string $columnName)
    {
        return $this->query("SELECT
                    s.name AS statistics_name,
                    c.name AS column_name,
                    t.name AS table_name
                FROM sys.stats AS s
                JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
                JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
                JOIN sys.tables AS t ON s.object_id = t.object_id
                WHERE
                    t.name = '{$tableName}' AND
                    c.name = '{$columnName}'")->fetchAll(\PDO::FETCH_OBJ);
    }

    /**
     * Check if the informed table.column has statistics
     * @param string $tableName
     * @param string $columnName
     * @return bool
     */
    public function hasStatistic(string $tableName, string $columnName): bool
    {
        return count($this->getStatistics($tableName, $columnName)) > 0;
    }

    public function dropStatistic(string $tableName, string $columnName)
    {
        $return = true;
        $statistics = $this->getStatistics($tableName, $columnName);
        foreach ($statistics as $statistic) {
            if ($statistic->table_name === $tableName && $statistic->column_name === $columnName) {
                $this->query("DROP STATISTICS {$tableName}.{$columnName}")->execute();
            }
        }

        return $return;
    }

    public function createStatistic(string $tableName, string $columnName)
    {
        return $this->query("CREATE STATISTICS {$columnName} ON {$tableName}({$columnName})")->execute();
    }
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions