Skip to main content

Справочник по MSSQL

Основные команды для администрирования и работы с базами данных MS SQL Server.

Создание и удаление баз данных

Создание БД (`CREATE DATABASE`)

Самый простой способ создать новую базу данных. MS SQL Server использует настройки по умолчанию для расположения файлов.

CREATE DATABASE MyNewDatabase;

Ситуация из жизни: Вы разворачиваете новое веб-приложение, и ему требуется собственная изолированная база данных для хранения пользовательских данных, настроек и контента.

Более продвинутый способ — явно указать пути для файлов данных (.mdf) и логов (.ldf). Это улучшает производительность, если разнести файлы на разные физические диски.

CREATE DATABASE MyWebAppDB
ON
( NAME = 'MyWebApp_Data', FILENAME = 'C:\SQLData\MyWebApp.mdf' )
LOG ON
( NAME = 'MyWebApp_Log', FILENAME = 'C:\SQLLogs\MyWebApp.ldf' );

Удаление БД (`DROP DATABASE`)

Команда для полного удаления базы данных. Внимание! Эта команда необратима. Все данные и файлы журнала будут удалены навсегда. Всегда делайте резервную копию перед удалением.

DROP DATABASE MyNewDatabase;

Ситуация из жизни: Вы выводите из эксплуатации старое приложение, и его база данных больше не нужна. Удаление освобождает дисковое пространство и системные ресурсы.

Проверка целостности и ремонт (`DBCC CHECKDB`)

Тестирование без исправления

"Безопасный" режим. Он только сообщает об ошибках, но не пытается ничего исправлять. Это первый шаг в диагностике.

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Ситуация из жизни: Вы проводите еженедельную плановую проверку баз данных, чтобы убедиться в их "здоровье" без прерывания работы.

Ремонт с минимальным риском (`REPAIR_REBUILD`)

Перед ремонтом базу необходимо перевести в однопользовательский режим. Эта команда исправляет незначительные ошибки (например, в индексах) без риска потери данных.

-- Перевод в однопользовательский режим
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Выполнение ремонта
DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);

-- Возврат в многопользовательский режим
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Ситуация из жизни: `DBCC CHECKDB` нашел ошибки в некластеризованном индексе. `REPAIR_REBUILD` перестроит этот индекс, не затронув сами данные.

Ремонт с возможной потерей данных (`REPAIR_ALLOW_DATA_LOSS`)

Внимание! Это крайняя мера. Используйте ее, только если нет свежей резервной копии. Эта опция может удалить поврежденные страницы данных, чтобы вернуть базу в рабочее состояние. Потеря данных почти гарантирована.

-- Перевод в однопользовательский режим
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Выполнение ремонта с риском потери данных
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

-- Возврат в многопользовательский режим
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Ситуация из жизни: Произошел сбой диска, резервной копии нет. База данных не запускается. Чтобы спасти хоть какие-то данные и запустить базу, вы вынуждены применить этот метод, смирившись с потерей части информации.

Поиск данных и объектов

Поиск таблиц по имени

Используйте системное представление `INFORMATION_SCHEMA.TABLES` для поиска таблиц по шаблону.

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%Users%';

Ситуация из жизни: Вы работаете с незнакомой базой данных и вам нужно найти все таблицы, связанные с пользователями. Поиск по части имени (`%Users%`) помогает быстро сориентироваться.

Поиск значения во всех текстовых полях

Этот скрипт использует курсор и динамический SQL, чтобы найти заданное значение во всех полях типа `(n)char`, `(n)varchar`, `(n)text` во всех таблицах базы.

Измените значение переменной `@SearchStr` на искомое. `%` — это символ-джокер.

-- =============================================
-- Автор:      stackoverflow.com (адаптировано)
-- Описание:   Поиск текстового значения во всех текстовых полях всех таблиц БД
-- =============================================

-- Параметр: искомое значение
DECLARE @SearchStr nvarchar(100) = '%ПоискЗначения%';

-- Создаем временную таблицу для хранения результатов
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630));

SET NOCOUNT ON;

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110);
SET  @TableName = '';
-- Заменяем одинарные кавычки для использования в запросе
SET @SearchStr2 = REPLACE(@SearchStr, '''', '''''');

-- Курсор для перебора всех текстовых полей во всех таблицах
WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = '';
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    );

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        );

        -- Выполняем динамический SQL для поиска в текущем поле
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
            );
        END
    END
END

-- Выводим результаты
SELECT ColumnName, ColumnValue FROM #Results;

-- Удаляем временную таблицу
DROP TABLE #Results;

Ситуация из жизни: К вам пришла жалоба от пользователя 'Иванов Иван' на некорректное отображение данных. Вы не знаете, в какой из сотен таблиц хранится его профиль. Этот скрипт пройдется по всей базе и найдет все таблицы и столбцы, где встречается строка 'Иванов Иван'.

Изменение данных

Обновление записей (`UPDATE`)

Команда для изменения данных в существующих строках. Крайне важно всегда использовать условие `WHERE`, чтобы не изменить все записи в таблице.

-- Увеличить цену на 10% для всех товаров в категории с ID = 5
UPDATE Products 
SET Price = Price * 1.10 
WHERE CategoryID = 5;

Ситуация из жизни: Вам нужно поднять цены на 10% для всех товаров в категории 'Электроника' (с ID = 5).

Замена части строки (`REPLACE`)

Функция `REPLACE` позволяет найти и заменить часть строки внутри поля. Она часто используется вместе с `UPDATE`.

-- Заменить все вхождения старого домена на новый в текстах статей
UPDATE Articles 
SET Content = REPLACE(Content, 'http://old-domain.com', 'https://new-domain.com') 
WHERE Content LIKE '%http://old-domain.com%';

Ситуация из жизни: Компания переехала на новый домен. Вам нужно массово обновить все старые ссылки в текстах статей в базе данных.

Back to top