[MSSQL2000]PK확인

DECLARE @TABLE_NAME AS VARCHAR(30)

SET @TABLE_NAME = 'tbl_research_admin'

 

SELECT

         [컬럼명] = D.COLUMN_DESC

       , [컬럼ID] = C.COLUMN_NAME

       , [Type & Length] = COLUMN_TYPE

       , [NOT NULL] = CASE WHEN C.IS_NULLABLE = 'NO' THEN 'Y' ELSE '' END

       , PK = ISNULL(K.PK, '')

       , FK = ISNULL(K.FK, '')

       , INX = ISNULL(K.INX, '')

FROM

(

 SELECT 

    TABLE_NAME

  , COLUMN_NAME

  , IS_NULLABLE

  , COLUMN_TYPE = CASE WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION)) + ')'

      WHEN DATA_TYPE IN ('DECIMAL') THEN

       DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'      

      ELSE DATA_TYPE END

  , ORDINAL_POSITION

 FROM INFORMATION_SCHEMA.COLUMNS

 WHERE TABLE_NAME = @TABLE_NAME

) AS C

LEFT JOIN -- 키정보

(

       SELECT COLUMN_NAME

             , PK = MAX(PK)

             , FK = MAX(FK)

             , INX = MAX(INX)

       FROM

       (

             SELECT

                      COLUMN_NAME

                    , PK = CASE WHEN K.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Y' END

                    , FK = CASE WHEN K.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'Y' END

                    , INX = CASE WHEN K.CONSTRAINT_TYPE = 'INDEX' THEN 'Y' END

              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K

                    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME

              WHERE K.TABLE_NAME = @TABLE_NAME

       ) AS K

       GROUP BY COLUMN_NAME

) AS K ON C.COLUMN_NAME = K.COLUMN_NAME

LEFT JOIN -- Description 정보

(

 SELECT

    COLUMN_NM = isnull(objname, '')

  , COLUMN_DESC = isnull(value, '')

 from ::fn_listextendedproperty(NULL, N'USER', N'dbo', N'TABLE', @TABLE_NAME, N'COLUMN', null)

) AS D ON C.COLUMN_NAME = D.COLUMN_NM

ORDER BY C.ORDINAL_POSITION