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

[특정 테이블 정보 쉽게 확인 Query]

set ANSI_NULLS ON

set 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.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;

=========================================================================================================

해당 작업 완료 후 도구 옵션에서 키보드 지정해 놓으면 쉽게 쓸 수 있음