반응형
(1) 특정 저장프로시저에서 사용하는 테이블 목록
| DECLARE @v_SPName NVARCHAR(50) SET @v_SPName = 'usp_web_UserProcess' ;WITH stored_procedures AS ( SELECT oo.name AS TABLE_NAME, ROW_NUMBER() OVER(PARTITION BY o.name, oo.name ORDER BY o.name, oo.name) AS row FROM sysdepends AS d INNER JOIN sysobjects AS o ON o.id = d.id INNER JOIN sysobjects AS oo ON oo.id = d.depid WHERE o.xtype = 'P' AND o.name LIKE '%' + @v_SPName + '%' AND oo.xtype = 'U' ) SELECT TABLE_NAME FROM stored_procedures WHERE row = 1 ORDER BY TABLE_NAME ASC |
(2) 테이블 별 ROW 수 얻기
SELECT o.name AS [TableName], REPLACE(CONVERT(NVARCHAR(50), CAST(i.rows AS MONEY), 1), '.00', '') AS [RowCount] FROM sys.sysindexes AS i INNER JOIN sys.sysobjects AS o ON i.id = o.id WHERE i.indid < 2 AND o.xtype = 'U' ORDER BY i.rows DESC |
(3) 테이블 별 용량 얻기
SELECT MIN(o.name) AS [TableName], (LTRIM(STR(ROUND(SUM(CAST(i.reserved AS BIGINT)) * 8192 / 1024.0 / 1024.0, 2), 15, 2)) + ' MB') AS [TableSize(MB)] FROM sys.sysindexes AS i INNER JOIN sys.sysobjects AS o ON o.id = i.id WHERE i.indid IN(0, 1, 255) AND o.xtype = 'U' GROUP BY i.id ORDER BY SUM(CAST(i.reserved AS BIGINT)) DESC |
(4) ROW 수 많은 순서대로 해당 테이블을 사용하는 저장프로시저 얻기
SELECT m.[TABLE_NAME], REPLACE(CONVERT(NVARCHAR(50), CAST(m.[TABLE_ROW_COUNT] AS MONEY), 1), '.00', '') AS [TABLE_ROW_COUNT], n.[SP_NAME] FROM ( SELECT a.[TABLE_NAME], a.[TABLE_ROW_COUNT], b.[DEP_OBJECT_ID] FROM ( SELECT o.id AS [OBJECT_ID], o.name AS [TABLE_NAME], i.rows AS [TABLE_ROW_COUNT] FROM sys.sysindexes AS i INNER JOIN sys.sysobjects AS o ON o.id = i.id WHERE o.xtype = 'U' AND o.name <> 'sysdiagrams' AND i.indid < 2 ) AS a INNER JOIN ( SELECT id AS [DEP_OBJECT_ID], depid AS [DEP_ID] FROM sysdepends ) AS b ON a.[OBJECT_ID] = b.[DEP_ID] ) AS m INNER JOIN ( SELECT id AS [SP_OBJECT_ID], name AS [SP_NAME] FROM sysobjects WHERE xtype = 'P' ) AS n ON m.[DEP_OBJECT_ID] = n.[SP_OBJECT_ID] WHERE m.[TABLE_ROW_COUNT] > 0 GROUP BY m.[TABLE_NAME], m.[TABLE_ROW_COUNT], n.[SP_NAME] ORDER BY m.[TABLE_ROW_COUNT] DESC, m.[TABLE_NAME] ASC, n.[SP_NAME] ASC |
반응형