MSSQL 관리를 위한 쿼리들

테이블 별 row 개수 확인

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

테이블 별 사용량 확인

<br/> SELECT<br/> table_name = CONVERT(varchar(30), min(o.name))<br/> ,table_size = LTRIM(str(sum(reserved) * 8192 / 1024., 15, 0) + 'KB')<br/>FROM<br/> sysindexes i INNER JOIN<br/> sysobjects o ON o.id = i.id<br/>WHERE<br/> i.indid in (0, 1, 255)<br/> AND o.xtype = 'U'<br/>GROUP BY i.id<br/>ORDER BY table_name<br/>




데이터베이스 내의 모든 테이블 drop 하기

<br/> DECLARE tables_cursor CURSOR<br/>FOR SELECT name FROM sysobjects WHERE type = 'U'<br/><br/>OPEN tables_cursor<br/>DECLARE @tablename sysname<br/><br/>FETCH NEXT FROM tables_cursor INTO @tablename<br/>WHILE (@@FETCH_STATUS <> -1)<br/>BEGIN<br/>EXEC ('DROP TABLE ' + @tablename)<br/>FETCH NEXT FROM tables_cursor INTO @tablename<br/>END<br/><br/>DEALLOCATE tables_cursor<br/><br/>PRINT 'All user-defined tables have been dropped from the database.'<br/>



데이터베이스 내의 모든 테이블 row delete 하기 (위의 것을 약간 응용해서...)

<br/> DECLARE tables_cursor CURSOR<br/>FOR SELECT name FROM sysobjects WHERE type = 'U'<br/><br/>OPEN tables_cursor<br/>DECLARE @tablename sysname<br/><br/>FETCH NEXT FROM tables_cursor INTO @tablename<br/>WHILE (@@FETCH_STATUS <> -1)<br/>BEGIN<br/>EXEC ('DELETE ' + @tablename)<br/>FETCH NEXT FROM tables_cursor INTO @tablename<br/>END<br/><br/>DEALLOCATE tables_cursor<br/><br/>PRINT 'All user-defined tables have been deleted tuples from the database.'<br/>





참고자료:

http://www.holisticsystems.co.uk/blog/?p=3