MSSQL 관리를 위한 쿼리들

테이블 별 row 개수 확인

SELECT o.name , i.rows FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.indid < 2 AND o.xtype = 'U' ORDER BY o.name

테이블 별 사용량 확인
SELECT table_name = CONVERT(varchar(30), min(o.name)) ,table_size = LTRIM(str(sum(reserved) * 8192 / 1024., 15, 0) + 'KB') 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 table_name



데이터베이스 내의 모든 테이블 drop 하기
DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC ('DROP TABLE ' + @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END DEALLOCATE tables_cursor PRINT 'All user-defined tables have been dropped from the database.'


데이터베이스 내의 모든 테이블 row delete 하기 (위의 것을 약간 응용해서...)
DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC ('DELETE ' + @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END DEALLOCATE tables_cursor PRINT 'All user-defined tables have been deleted tuples from the database.'




참고자료: http://www.holisticsystems.co.uk/blog/?p=3