테이블정보 뽑는 쿼리

-- 테이블 정보 조회

DECLARE @TABLE_NAME NVARCHAR(50) = '테이블명';

SELECT D.COLORDER                AS COLUMN_IDX            -- Column Index
     , A.NAME                    AS TABLE_NAME            -- Table Name
     , C.VALUE                    AS TABLE_DESCRIPTION    -- Table Description
     , D.NAME                    AS '컬럼 ID' --COLUMN_NAME            -- Column Name
     , E.VALUE                    AS '컬럼명' -- COLUMN_DESCRIPTION    -- Column Description
     , F.DATA_TYPE                AS 'TYPE'                    -- Column Type
     , F.CHARACTER_OCTET_LENGTH    AS 'LEN'                -- Column Length
     , CASE WHEN F.IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END          AS 'NOT NULL'            -- Column Nullable
     --, F.COLLATION_NAME            AS COLLATION_NAME        -- Column Collaction Name
  FROM SYSOBJECTS A WITH (NOLOCK)
  INNER JOIN SYSUSERS B WITH (NOLOCK)        ON A.UID = B.UID
  INNER JOIN SYSCOLUMNS D WITH (NOLOCK)        ON D.ID = A.ID
  INNER JOIN INFORMATION_SCHEMA.COLUMNS F WITH (NOLOCK)
     ON A.NAME = F.TABLE_NAME
    AND D.NAME = F.COLUMN_NAME
   LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C WITH (NOLOCK)
     ON C.MAJOR_ID = A.ID
    AND C.MINOR_ID = 0
    AND C.NAME = 'MS_Description'
   LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E WITH (NOLOCK)
     ON E.MAJOR_ID = D.ID
    AND E.MINOR_ID = D.COLID
    AND E.NAME = 'MS_Description'  
  WHERE 1=1
    AND A.TYPE = 'U'
    AND A.NAME = @TABLE_NAME
  ORDER BY D.COLORDER
  
 

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%  %';