CMssqlSchema

public function alterColumn($table, $column, $type)
{
    $type=$this->getColumnType($type);
    $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' ALTER COLUMN '
        . $this->quoteColumnName($column) . ' '
        . $this->getColumnType($type);
    return $sql;
}

public function checkIntegrity($check=true,$schema='')
{
    $enable=$check ? 'CHECK' : 'NOCHECK';
    if(!isset($this->_normalTables[$schema]))
        $this->_normalTables[$schema]=$this->findTableNames($schema,false);
    $db=$this->getDbConnection();
    foreach($this->_normalTables[$schema] as $tableName)
    {
        $tableName=$this->quoteTableName($tableName);
        $db->createCommand("ALTER TABLE $tableName$enable CONSTRAINT ALL")->execute();
    }
}

public function compareTableNames($name1,$name2)
{
    $name1=str_replace(array('[',']'),'',$name1);
    $name2=str_replace(array('[',']'),'',$name2);
    return parent::compareTableNames(strtolower($name1),strtolower($name2));
}

protected function createColumn($column)
{
    $c=new CMssqlColumnSchema;
    $c->name=$column['COLUMN_NAME'];
    $c->rawName=$this->quoteColumnName($c->name);
    $c->allowNull=$column['IS_NULLABLE']=='YES';
    if ($column['NUMERIC_PRECISION_RADIX']!==null)
    {
        // We have a numeric datatype
        $c->size=$c->precision=$column['NUMERIC_PRECISION']!==null?(int)$column['NUMERIC_PRECISION']:null;
        $c->scale=$column['NUMERIC_SCALE']!==null?(int)$column['NUMERIC_SCALE']:null;
    }
    elseif ($column['DATA_TYPE']=='image' || $column['DATA_TYPE']=='text')
        $c->size=$c->precision=null;
    else
        $c->size=$c->precision=($column['CHARACTER_MAXIMUM_LENGTH']!== null)?(int)$column['CHARACTER_MAXIMUM_LENGTH']:null;
    $c->autoIncrement=$column['IsIdentity']==1;
    $c->comment=$column['Comment']===null ? '' : $column['Comment'];

    $c->init($column['DATA_TYPE'],$column['COLUMN_DEFAULT']);
    return $c;
}

protected function createCommandBuilder()
{
    return new CMssqlCommandBuilder($this);
}

protected function findColumns($table)
{
    $columnsTable="INFORMATION_SCHEMA.COLUMNS";
    $where=array();
    $where[]="t1.TABLE_NAME='".$table->name."'";
    if (isset($table->catalogName))
    {
        $where[]="t1.TABLE_CATALOG='".$table->catalogName."'";
        $columnsTable = $table->catalogName.'.'.$columnsTable;
    }
    if (isset($table->schemaName))
        $where[]="t1.TABLE_SCHEMA='".$table->schemaName."'";

    $sql="SELECT t1.*, columnproperty(object_id(t1.table_schema+'.'+t1.table_name), t1.column_name, 'IsIdentity') AS IsIdentity, ".
         "CONVERT(VARCHAR, t2.value) AS Comment FROM ".$this->quoteTableName($columnsTable)." AS t1 ".
         "LEFT OUTER JOIN sys.extended_properties AS t2 ON t1.ORDINAL_POSITION = t2.minor_id AND ".
         "object_name(t2.major_id) = t1.TABLE_NAME AND t2.class=1 AND t2.class_desc='OBJECT_OR_COLUMN' AND t2.name='MS_Description' ".
         "WHERE ".join(' AND ',$where);
    try
    {
        $columns=$this->getDbConnection()->createCommand($sql)->queryAll();
        if(empty($columns))
            return false;
    }
    catch(Exception $e)
    {
        return false;
    }

    foreach($columns as $column)
    {
        $c=$this->createColumn($column);
        if (is_array($table->primaryKey))
            $c->isPrimaryKey=in_array($c->name, $table->primaryKey);
        else
            $c->isPrimaryKey=strcasecmp($c->name,$table->primaryKey)===0;

        $c->isForeignKey=isset($table->foreignKeys[$c->name]);
        $table->columns[$c->name]=$c;
        if ($c->autoIncrement && $table->sequenceName===null)
            $table->sequenceName=$table->name;
    }
    return true;
}

protected function findForeignKeys($table)
{
    $rc='INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS';
    $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
    if (isset($table->catalogName))
    {
        $kcu=$table->catalogName.'.'.$kcu;
        $rc=$table->catalogName.'.'.$rc;
    }

    //From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx
    $sql = <<<EOD
    SELECT
         KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
       , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
       , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
       , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
       , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
       , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
       , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
       , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
    FROM {$this->quoteTableName($rc)} RC
    JOIN {$this->quoteTableName($kcu)} KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
    JOIN {$this->quoteTableName($kcu)} KCU2
    ON KCU2.CONSTRAINT_CATALOG =
    RC.UNIQUE_CONSTRAINT_CATALOG
       AND KCU2.CONSTRAINT_SCHEMA =
    RC.UNIQUE_CONSTRAINT_SCHEMA
       AND KCU2.CONSTRAINT_NAME =
    RC.UNIQUE_CONSTRAINT_NAME
       AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
    WHERE KCU1.TABLE_NAME = :table
EOD;
    $command = $this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table', $table->name);
    $fkeys=array();
    foreach($command->queryAll() as $info)
    {
        $fkeys[$info['FK_COLUMN_NAME']]=array($info['UQ_TABLE_NAME'],$info['UQ_COLUMN_NAME'],);

    }
    return $fkeys;
}

protected function findPrimaryKey($table)
{
    $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
    $tc='INFORMATION_SCHEMA.TABLE_CONSTRAINTS';
    if (isset($table->catalogName))
    {
        $kcu=$table->catalogName.'.'.$kcu;
        $tc=$table->catalogName.'.'.$tc;
    }

    $sql = <<<EOD
    SELECT k.column_name field_name
    FROM {$this->quoteTableName($kcu)} k
    LEFT JOIN {$this->quoteTableName($tc)} c
    ON k.table_name = c.table_name
        AND k.constraint_name = c.constraint_name
    WHERE c.constraint_type ='PRIMARY KEY'
        AND k.table_name = :table
        AND k.table_schema = :schema
EOD;
    $command = $this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table', $table->name);
    $command->bindValue(':schema', $table->schemaName);
    $primary=$command->queryColumn();
    switch (count($primary))
    {
        case 0: // No primary key on table
            $primary=null;
            break;
        case 1: // Only 1 primary key
            $primary=$primary[0];
            break;
    }
    return $primary;
}

protected function findTableNames($schema='',$includeViews=true)
{
    if($schema==='')
        $schema=self::DEFAULT_SCHEMA;
    if($includeViews)
        $condition="TABLE_TYPE in ('BASE TABLE','VIEW')";
    else
        $condition="TABLE_TYPE='BASE TABLE'";
    $sql=<<<EOD
SELECT TABLE_NAME FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_SCHEMA=:schema AND $condition
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    $command->bindParam(":schema", $schema);
    $rows=$command->queryAll();
    $names=array();
    foreach ($rows as $row)
    {
        if ($schema == self::DEFAULT_SCHEMA)
            $names[]=$row['TABLE_NAME'];
        else
            $names[]=$schema.'.'.$row['TABLE_NAME'];
    }

    return $names;
}

protected function loadTable($name)
{
    $table=new CMssqlTableSchema;
    $this->resolveTableNames($table,$name);
    //if (!in_array($table->name, $this->tableNames)) return null;
    $table->primaryKey=$this->findPrimaryKey($table);
    $table->foreignKeys=$this->findForeignKeys($table);
    if($this->findColumns($table))
    {
        return $table;
    }
    else
        return null;
}

public function quoteSimpleColumnName($name)
{
    return '['.$name.']';
}

public function quoteSimpleTableName($name)
{
    return '['.$name.']';
}

public function renameColumn($table, $name, $newName)
{
    return "sp_rename '$table.$name', '$newName', 'COLUMN'";
}

public function renameTable($table, $newName)
{
    return "sp_rename '$table', '$newName'";
}

public function resetSequence($table,$value=null)
{
    if($table->sequenceName===null)
        return;
    if($value!==null)
        $value=(int)($value)-1;
    else
        $value=(int)$this->getDbConnection()
            ->createCommand("SELECT MAX([{$table->primaryKey}]) FROM {$table->rawName}")
            ->queryScalar();
    $name=strtr($table->rawName,array('['=>'',']'=>''));
    $this->getDbConnection()
        ->createCommand("DBCC CHECKIDENT ('$name',RESEED,$value)")
        ->execute();
}

protected function resolveTableNames($table,$name)
{
    $parts=explode('.',str_replace(array('[',']'),'',$name));
    if(($c=count($parts))==3)
    {
        // Catalog name, schema name and table name provided
        $table->catalogName=$parts[0];
        $table->schemaName=$parts[1];
        $table->name=$parts[2];
        $table->rawName=$this->quoteTableName($table->catalogName).'.'.$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
    elseif ($c==2)
    {
        // Only schema name and table name provided
        $table->name=$parts[1];
        $table->schemaName=$parts[0];
        $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
    else
    {
        // Only the name given, we need to get at least the schema name
        //if (empty($this->_schemaNames)) $this->findTableNames();
        $table->name=$parts[0];
        $table->schemaName=self::DEFAULT_SCHEMA;
        $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
}