DB - MSSQL - 테이블 인덱스 재정리

-----------------------------------------------------------------------------------
해당 데이터베이스의 모든 테이블의 모든 인덱스를 재정리
-----------------------------------------------------------------------------------

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor





-----------------------------------------------------------------------------------
특정한 테이블 인텍스 재정리
-----------------------------------------------------------------------------------

USE DB명

SP_HELPINDEX table명



--조회
DBCC SHOWCONTIG (table명) WITH TABLERESULTS

/*

Scan Density(logical extent density) 와 Avg Page Density(Avg free bytes/page) 확인.

30~50% under이면 Reorg가 필요

*/

--index defrag

DBCC INDEXDEFRAG(DB명, TABLE명, INDEX명)
--혹은 dbcc dbreindex(TABLE명, '', 100)

--reindex 후 다시 조회
DBCC SHOWCONTIG (SC_TRAN) WITH TABLERESULTS

DB - MSSQL - tables & columns description

[테이블 및 컬럼 설명 보기]

select a.table_name, a.table_comment, b.column_name, b.column_description
from (
SELECT
tbl.name as table_name
, p.value AS table_comment
FROM sys.tables AS tbl
inner join sys.schemas as s on s.schema_id = tbl.schema_id
LEFT JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id AND p.minor_id = 0 AND p.class = 1 AND p.name = 'MS_Description'
where p.value is not null
) a
left join (
SELECT O.name AS [table_name], c.name as [column_name], ep.value AS [column_description]
FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
where c.name is not null
) b on a.table_name = b.table_name
where b.column_name is not null
order by a.table_name, b.column_name