[MSSQL] 테이블 레이아웃 조회

SELECT A.NAME AS TABLEID
, (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WITH(NOLOCK) WHERE MAJOR_ID = A.ID AND MINOR_ID = 0) AS TABLENAME
, B.colid AS COLNO
, B.name AS COLID
, (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WITH(NOLOCK) WHERE MAJOR_ID = A.ID AND MINOR_ID = B.colid) AS COLNAME
, D.KEYNO AS PKORDER
, C.name AS DATATYPE
, CASE
WHEN C.xprec = 0
THEN RTRIM(CONVERT(CHAR,B.LENGTH))
ELSE RTRIM(CONVERT(CHAR,B.XPREC))+','+RTRIM(CONVERT(CHAR,B.XSCALE))
END AS SIZE
, CASE
WHEN B.ISNULLABLE = 0
THEN 'N'
ELSE 'Y'
END AS ISNULLABLE
, (SELECT ROWS FROM SYSINDEXES WITH(NOLOCK) WHERE INDID < 2 AND ID = A.ID) AS ROWS
, E.MB --MByte
, I1.KEYNO AS IX1
, I2.KEYNO AS IX2
, I3.KEYNO AS IX3
, I4.KEYNO AS IX4
, I5.KEYNO AS IX5
, C.xprec
, B.length
, B.xscale

FROM SYSOBJECTS A WITH(NOLOCK)

LEFT JOIN SYSCOLUMNS B WITH(NOLOCK)
ON A.ID = B.ID

LEFT JOIN SYSTYPES C WITH(NOLOCK)
ON B.XUSERTYPE = C.xusertype

LEFT JOIN DBO.SYSINDEXKEYS D WITH(NOLOCK)
ON B.id = D.id
AND B.colid = D.colid
AND D.indid = 1

LEFT JOIN
(
SELECT ID
, SUM(CONVERT(NUMERIC,RESERVED)) * 8192 / 1024 / 1024 AS MB --MByte
FROM SYSINDEXES WITH(NOLOCK)
WHERE INDID IN (0,1,255)
GROUP BY ID
) E
ON A.ID = E.ID

LEFT JOIN
(
SELECT A.*
, DENSE_RANK() OVER (PARTITION BY ID ORDER BY INDID) AS PNO
FROM SYSINDEXKEYS A WITH(NOLOCK)
WHERE INDID > 1
) I1
ON B.ID = I1.ID
AND B.COLID = I1.COLID
AND I1.PNO = 1

LEFT JOIN
(
SELECT A.*
, DENSE_RANK() OVER (PARTITION BY ID ORDER BY INDID) AS PNO
FROM SYSINDEXKEYS A WITH(NOLOCK)
WHERE INDID > 1
) I2
ON B.ID = I2.ID
AND B.COLID = I2.COLID
AND I2.PNO = 2

LEFT JOIN
(
SELECT A.*
, DENSE_RANK() OVER (PARTITION BY ID ORDER BY INDID) AS PNO
FROM SYSINDEXKEYS A WITH(NOLOCK)
WHERE INDID > 1
) I3
ON B.ID = I3.ID
AND B.COLID = I3.COLID
AND I3.PNO = 3

LEFT JOIN
(
SELECT A.*
, DENSE_RANK() OVER (PARTITION BY ID ORDER BY INDID) AS PNO
FROM SYSINDEXKEYS A WITH(NOLOCK)
WHERE INDID > 1
) I4
ON B.ID = I4.ID
AND B.COLID = I4.COLID
AND I4.PNO = 4

LEFT JOIN
(
SELECT A.*
, DENSE_RANK() OVER (PARTITION BY ID ORDER BY INDID) AS PNO
FROM SYSINDEXKEYS A WITH(NOLOCK)
WHERE INDID > 1
) I5
ON B.ID = I5.ID
AND B.COLID = I5.COLID
AND I5.PNO = 5

WHERE A.XTYPE = 'U'
ORDER BY 1, 3
;