재우니의 블로그

이태원 참사에 대한 깊은 애도를 표합니다. 티스토리측에서도 공지했듯 이태원 참사 관련으로 콘텐츠를 생산하는분이 없기를 바랍니다.

반응형

 

 

도구 > 옵션 메뉴에서 '환경'- > '키보드' -> '쿼리 바로가기' 부분에서 보시면 됩니다.

 

 

Alt+F1  sp_help
Ctrl+F1 sp_helpindex
Ctrl+1 	sp_who
Ctrl+2 	sp_lock

Ctrl+3 	sp_helptext 

Ctrl+4 	sp_helpstatus 

Ctrl+5 	select top 1000 * from

Ctrl+6 	sp_spaceUsed 
Ctrl+8 	set statistics profile on;set statistics io on;set statistics time on;
Ctrl+9 	set statistics profile off;set statistics io off;set statistics time off;

 

 

 

 


 

이걸 단축키 지정해서 별도로 사용해도 좋을 내용입니다.

sp_help 에 안보이는 '설명' 내용을 볼 수 있도록 확장된 버전으로 보시면 되겠습니다.

ALTER   PROC [dbo].[sp_HelpTable]  

( @P_TABNAME VARCHAR(100))  

AS  

SELECT TB.NAME,     
       CAST(q.[VALUE] AS VARCHAR(1000)) AS TAB_CMT    
FROM SYS.OBJECTS TB   
	LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES q  ON (q.MAJOR_ID = tb.OBJECT_ID   
                                               AND q.MINOR_ID = 0  
                                               AND q.CLASS = 1   
                                               AND q.NAME = 'MS_Description')  
WHERE TB.NAME = LTRIM(RTRIM(@P_TABNAME))  


SELECT clmns.name as [Column Name],
       ISNULL(CAST(ep.value AS nvarchar(4000)), '') as [Description],
       clmns.column_id as [ColumnID],
 	   CASE WHEN usrt.system_type_id IS NULL THEN 'Null'
		    WHEN (usrt.system_type_id <> usrt.user_type_id) AND (usrt.name = 'sysname') THEN 'sysname'
		    ELSE ISNULL(baset.name, '') END as [Type],
 	   CAST(CASE WHEN (baset.name in ('nchar', 'nvarchar')) AND (clmns.max_length <> -1) THEN clmns.max_length / 2
                 ELSE clmns.max_length END AS int) as [Length],
       CAST(clmns.precision AS int) as [NumericPrecision],
       CAST(clmns.scale AS int) as [NumericScale],
       CAST(ISNULL(cik.index_column_id, 0) AS bit) as [PrimaryKey],
       CAST(ISNULL(cik.key_ordinal, 0) AS int) as [PosInPKey],
       CASE WHEN cik.key_ordinal IS NULL THEN ''
	        WHEN cik.is_descending_key = 0 THEN 'A'
			ELSE 'D' END as [OrderInPKey],
       CASE WHEN clmns.is_nullable = 0 THEN 'NOTNULL' ELSE 'NULL' END as [Not Null],
	   ISNULL(clmns.collation_name, '') as [Collation] 
FROM
  sys.all_objects o INNER JOIN sys.schemas sc ON (sc.schema_id = o.schema_id)
                    INNER JOIN sys.all_columns clmns ON (clmns.object_id = o.object_id)
                    LEFT OUTER JOIN sys.identity_columns ic ON ((ic.object_id = o.object_id) AND
                                                               (ic.column_id = clmns.column_id))
                    LEFT OUTER JOIN sys.computed_columns cc ON ((cc.object_id = o.object_id) AND
                                                               (cc.column_id = clmns.column_id))
                    LEFT OUTER JOIN sys.all_objects o1 ON (o1.object_id = clmns.default_object_id)
                    LEFT OUTER JOIN sys.all_objects o2 ON (o2.object_id = clmns.rule_object_id)
                    LEFT OUTER JOIN sys.check_constraints ch ON ((ch.parent_object_id = clmns.object_id) AND
                                                                (ch.parent_column_id = clmns.column_id))
                    LEFT OUTER JOIN sys.indexes ik ON ((ik.object_id = clmns.object_id) AND
                                                      (ik.is_primary_key = 1))
                    LEFT OUTER JOIN sys.index_columns cik ON ((cik.index_id = ik.index_id) AND
                                                             (cik.column_id = clmns.column_id) AND
                                                             (cik.object_id = clmns.object_id) AND
                                                             (cik.is_included_column = 0))
                    LEFT OUTER JOIN sys.types usrt ON (usrt.user_type_id = clmns.user_type_id)
                    LEFT OUTER JOIN sys.schemas scparam ON (scparam.schema_id = usrt.schema_id)
                    LEFT OUTER JOIN sys.types baset ON ((baset.user_type_id = clmns.system_type_id) AND
                                                       (baset.user_type_id = baset.system_type_id))
                    LEFT OUTER JOIN sys.extended_properties ep ON ((ep.major_id = o.object_id) AND
                                                                  (ep.minor_id = clmns.column_id) AND
                                                                  (ep.class = 1) AND
                                                                  (ep.name = 'MS_Description'))
                    LEFT OUTER JOIN sys.xml_schema_collections xscclmns ON (xscclmns.xml_collection_id = clmns.xml_collection_id)
                    LEFT OUTER JOIN sys.schemas s2clmns ON (s2clmns.schema_id = xscclmns.schema_id)
WHERE o.name = LTRIM(RTRIM(@P_TABNAME))  
ORDER BY clmns.column_id ASC;

 

 

해당 출처 : https://skystep.tistory.com/83

 

[MSSQL] TABLE 정보 쉽게 확인하기

[특정 테이블 정보 쉽게 확인 Query] set ANSI_NULLS ONset QUOTED_IDENTIFIER ON create PROC [dbo].[SP_HELPTABLE] ( @P_TABNAME VARCHAR(100)) AS SELECT TB.NAME, CAST(q.[VALUE] AS VARCHAR(1000)) AS TAB_CMT FROM SYS.OBJECTS TB LEFT OUTER JOIN SYS.EXTEN

skystep.tistory.com

 

 

반응형

댓글

비밀글모드