개기획자

테이블 별 사이즈 조회

--테이블 별 사이즈 조회 SELECT CONVERT(VARCHAR(30), MIN(o.name)) AS t_name , LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS t_size FROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE i.indid IN (0, 1, 255) AND o.xtype = 'U' GROUP BY i.id ORDER BY-- t_name ASC SUM(reserved) * 8192.0 / 1024.0 DESC

--모든 데이터베이스 크기 조회 SELECT sys.databases.name , CONVERT(VARCHAR,SUM(size)*8/1024/1024) +' GB' AS [Total disk space] FROM sys.databases JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id GROUP BY sys.databases.name ORDER BY [Total disk space], sys.databases.name

인덱스 정의서

--인덱스 정의서 select OBJECT_NAME(a.object_id) TableName, '' kortablename, a.name IndexName, TYPE_NAME(a.type) TypeName, Type_desc, is_unique , index_column_id colseq , COL_NAME(a.object_id, column_id) colName , (case when is_descending_key = 1 then 'DESC' else 'ASC' end) sortType from sys.indexes A join sys.index_columns B on a.object_id=b.object_id and a.index_id=b.index_id