Rutin işlemler için sık kullanılan SQL komutları

Merhaba, hatırlamakta zorlandığım ama belli aralıklarla da başvuru ihtiyacı hissettiğim SQL komutlarını burada listeliyorum. Sizin de işinize yaraması dileğiyle 🙂

Tablo Adı Değiştirme

EXEC sp_rename 'Routes.RelatedContentId', 'RelatedContentId', 'COLUMN'

Mevcut Tabloya Kolon Ekleme

ALTER TABLE Routes ADD RelatedContentTranslationId int NULL

Bütün Tabloların Schemasını Değiştirme

USE MY_TABLE_NAME

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)
DECLARE @newLine AS varchar(2) = CHAR(13) + CHAR(10)

SET @OldSchema = 'MY_OLD_SCHEMA_NAME'
SET @NewSchema = 'MY_NEW_SCHEMA_NAME'

DECLARE @sql AS varchar(MAX)

SET @sql = 'CREATE SCHEMA [' + @NewSchema + ']' + @newLine
SELECT @sql = @sql + 'GO' + @newLine
SELECT @sql = @sql + 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
     + @newLine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @OldSchema

SET @sql = @sql + 'DROP SCHEMA [' + @OldSchema + ']'

PRINT @sql -- NOTE PRINT HAS AN 8000 byte limit - 8000 varchar/4000 nvarchar - see comments
IF (0=1) EXEC (@sql)

Tabloların Index Bozulma Oranlarını Listeleme

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc

Index bozulma oranı %30 üzerinde olan tablolar için yeniden index oluşturma

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);
DECLARE @Schema varchar(255);

SELECT 
    dbtables.[name], 
    dbindexes.[name],
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count [pages]
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables 
        on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas 
        on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes 
        ON dbindexes.[object_id] = indexstats.[object_id]
        AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent >= 5.0
    AND indexstats.page_count > 10
ORDER BY 
    indexstats.page_count ASC,
    indexstats.avg_fragmentation_in_percent ASC

DECLARE TableCursor CURSOR FOR  
    SELECT 
        dbtables.[name], 
        dbindexes.[name],
        indexstats.avg_fragmentation_in_percent,
        dbschemas.[name]
    FROM 
        sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
        INNER JOIN sys.tables dbtables 
            on dbtables.[object_id] = indexstats.[object_id]
        INNER JOIN sys.schemas dbschemas 
            on dbtables.[schema_id] = dbschemas.[schema_id]
        INNER JOIN sys.indexes AS dbindexes 
            ON dbindexes.[object_id] = indexstats.[object_id]
            AND indexstats.index_id = dbindexes.index_id
    WHERE 
        indexstats.database_id = DB_ID()
        AND indexstats.avg_fragmentation_in_percent >= 5.0
        AND indexstats.page_count > 10
    ORDER BY 
        indexstats.page_count ASC,
        indexstats.avg_fragmentation_in_percent ASC;

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO
    @TableName,
    @IndexName,
    @Fragmentation,
    @Schema
 
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
    IF (@Fragmentation >= 30.0)
        SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REBUILD';
    ELSE IF (@Fragmentation >= 5.0)
        SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REORGANIZE';
    ELSE
        SET @IndexScript = NULL;

    IF (@IndexScript IS NOT NULL)
    BEGIN
        --SELECT @IndexScript
        RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
        WAITFOR DELAY '00:00:01';
        EXEC(@IndexScript); 
    END

    FETCH NEXT FROM TableCursor INTO
        @TableName,
        @IndexName,
        @Fragmentation,
        @Schema;
 
END 
 
CLOSE TableCursor;
 
DEALLOCATE TableCursor;