반응형
(1) 최근 수정 테이블, 저장프로시저 보기
| DECLARE @v_CreateDate VARCHAR(10) DECLARE @v_ModifyDate VARCHAR(10) SET @v_CreateDate = CONVERT(VARCHAR(10), GETDATE() - 1, 23) SET @v_ModifyDate = CONVERT(VARCHAR(10), GETDATE() - 1, 23) SELECT * FROM sys.tables WHERE CREATE_DATE >= @v_CreateDate OR MODIFY_DATE >= @v_ModifyDate ORDER BY MODIFY_DATE DESC SELECT * FROM sys.procedures WHERE CREATE_DATE >= @v_CreateDate OR MODIFY_DATE >= @v_ModifyDate ORDER BY MODIFY_DATE DESC |
(2) 특정 문자열(테이블)이 사용된 저장프로시저 찾기
| DECLARE @v_SearchString NVARCHAR(100) SET @v_SearchString = 'tbl_Member' SELECT OBJECT_NAME(object_id) AS sp FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%' + @v_SearchString + '%' |
(3) 특정 테이블 스키마 보기
| DECLARE @v_TableName NVARCHAR(50) SET @v_TableName = 'tbl_Member' SELECT m.f_SchemaName, m.f_TableName, n.f_Position, n.f_Name, n.f_DataType, n.f_DefaultValue, n.f_IsIdentity, n.f_Comment FROM ( SELECT object_id AS f_ObjectID, SCHEMA_NAME(schema_id) AS f_SchemaName, [name] AS f_TableName FROM sys.objects WHERE type IN('U', 'V') ) AS m LEFT OUTER JOIN ( SELECT a.object_id, a.column_id AS f_Position, a.name AS f_Name, (UPPER(TYPE_NAME(a.user_type_id)) + (CASE WHEN TYPE_NAME(a.user_type_id) = 'varchar' OR TYPE_NAME(a.user_type_id) = 'char' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'nvarchar' OR TYPE_NAME(a.user_type_id) = 'nchar' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length / 2 AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'decimal' THEN '(' + CAST(a.precision AS VARCHAR) + ', ' + CAST(a.scale AS VARCHAR) + ')' ELSE '' END)) AS f_DataType, (CASE WHEN a.is_nullable = 1 THEN 'Y' ELSE 'N' END) AS f_IsNullable, a.text AS f_DefaultValue, (CASE a.is_identity WHEN 1 THEN 'Identity()' ELSE '' END) AS f_IsIdentity, CAST(b.value AS VARCHAR(8000)) AS f_Comment FROM ( SELECT col.*, comm.text FROM sys.columns AS col LEFT OUTER JOIN sys.syscomments AS comm ON col.default_object_id = comm.id AND comm.colid = 1 ) AS a LEFT OUTER JOIN sys.extended_properties AS b ON a.object_id = b.major_id AND a.column_id = b.minor_id AND b.class = 1 AND b.name = 'MS_Description' ) AS n ON m.f_ObjectID = n.object_id WHERE m.f_TableName = @v_TableName ORDER BY n.f_Position ASC |
(4) 특정 컬럼이 있는 테이블 목록
| DECLARE @v_ColName NVARCHAR(50) SET @v_ColName = 'Idx' SELECT m.f_SchemaName, m.f_TableName, n.f_Position, n.f_Name, n.f_DataType, n.f_DefaultValue, n.f_IsIdentity, n.f_Comment FROM ( SELECT object_id AS f_ObjectID, SCHEMA_NAME(schema_id) AS f_SchemaName, [name] AS f_TableName FROM sys.objects WHERE type IN('U', 'V') ) AS m LEFT OUTER JOIN ( SELECT a.object_id, a.column_id AS f_Position, a.name AS f_Name, (UPPER(TYPE_NAME(a.user_type_id)) + (CASE WHEN TYPE_NAME(a.user_type_id) = 'varchar' OR TYPE_NAME(a.user_type_id) = 'char' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'nvarchar' OR TYPE_NAME(a.user_type_id) = 'nchar' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length / 2 AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'decimal' THEN '(' + CAST(a.precision AS VARCHAR) + ',' + CAST(a.scale AS VARCHAR) + ')' ELSE '' END)) AS f_DataType, (CASE WHEN a.is_nullable = 1 THEN 'Y' ELSE 'N' END) AS f_IsNullable, a.text AS f_DefaultValue, (CASE a.is_identity WHEN 1 THEN 'Identity()' ELSE '' END) AS f_IsIdentity, CAST(b.value AS VARCHAR(8000)) AS f_Comment FROM ( SELECT col.*, comm.text FROM sys.columns AS col LEFT OUTER JOIN sys.syscomments AS comm ON col.default_object_id = comm.id AND comm.colid = 1 ) AS a LEFT OUTER JOIN sys.extended_properties AS b ON a.object_id = b.major_id AND a.column_id = b.minor_id AND b.class = 1 AND b.name = 'MS_Description' ) AS n ON m.f_ObjectID = n.object_id WHERE n.f_Name LIKE '%' + @v_ColName + '%' |
(5) 특정 저장프로시저 스키마 보기
| DECLARE @v_SPName NVARCHAR(50) SET @v_SPName = 'usp_tools_MailReserve_Process' SELECT m.f_SchemaName, m.f_SPName, n.f_Position, n.f_Name, n.f_DataType, n.f_Mode, n.f_Comment FROM ( SELECT object_id AS f_ObjectID, SCHEMA_NAME(schema_id) AS f_SchemaName, [name] AS f_SPName FROM sys.objects WHERE type IN('P') ) AS m LEFT OUTER JOIN ( SELECT a.object_id, a.parameter_id AS f_Position, a.name AS f_Name, (UPPER(TYPE_NAME(a.user_type_id)) + (CASE WHEN TYPE_NAME(a.user_type_id) = 'varchar' OR TYPE_NAME(a.user_type_id) = 'char' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'nvarchar' OR TYPE_NAME(a.user_type_id) = 'nchar' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length / 2 AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'decimal' THEN '(' + CAST(a.precision AS VARCHAR) + ',' + CAST(a.scale AS VARCHAR) + ')' ELSE '' END)) AS f_DataType, (CASE WHEN a.parameter_id = 0 THEN 'OUT' WHEN a.is_output = 1 THEN 'INOUT' ELSE 'IN' END) AS f_Mode, CAST(b.value AS VARCHAR(8000)) AS f_Comment FROM sys.parameters AS a LEFT OUTER JOIN sys.extended_properties AS b ON a.object_id = b.major_id AND a.parameter_id = b.minor_id AND b.name = 'MS_Description' ) AS n ON m.f_ObjectID = n.object_id WHERE m.f_SPName LIKE '%' + @v_SPName + '%' ORDER BY n.f_Position ASC |
(6) 특정 파라미터 사용하는 저장프로시저 목록
| DECLARE @v_ParamName NVARCHAR(50) SET @v_ParamName = '@USN' SELECT m.f_SchemaName, m.f_SPName, n.f_Position, n.f_Name, n.f_DataType, n.f_Mode, n.f_Comment FROM ( SELECT object_id AS f_ObjectID, SCHEMA_NAME(schema_id) AS f_SchemaName, [name] AS f_SPName FROM sys.objects WHERE type IN('P') ) AS m LEFT OUTER JOIN ( SELECT a.object_id, a.parameter_id AS f_Position, a.name AS f_Name, (UPPER(TYPE_NAME(a.user_type_id)) + (CASE WHEN TYPE_NAME(a.user_type_id) = 'varchar' OR TYPE_NAME(a.user_type_id) = 'char' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'nvarchar' OR TYPE_NAME(a.user_type_id) = 'nchar' THEN '(' + (CASE WHEN a.max_length = -1 THEN 'MAX' ELSE CAST(a.max_length / 2 AS VARCHAR) END) + ')' WHEN TYPE_NAME(a.user_type_id) = 'decimal' THEN '(' + CAST(a.precision AS VARCHAR) + ',' + CAST(a.scale AS VARCHAR) + ')' ELSE '' END)) AS f_DataType, (CASE WHEN a.parameter_id = 0 THEN 'OUT' WHEN a.is_output = 1 THEN 'INOUT' ELSE 'IN' END) AS f_Mode, CAST(b.value AS VARCHAR(8000)) AS f_Comment FROM sys.parameters AS a LEFT OUTER JOIN sys.extended_properties AS b ON a.object_id = b.major_id AND a.parameter_id = b.minor_id AND b.name = 'MS_Description' ) AS n ON m.f_ObjectID = n.object_id WHERE n.f_Name LIKE '%' + @v_ParamName + '%' ORDER BY n.f_Position ASC |
반응형