MS SQL INDEX LIST 取得現有 MS SQL 的資料表的index清單

'取得現有 MS SQL 的資料表的index清單

SELECT TBL.NAME AS TABLE_NAME,
        I.NAME AS INDEX_NAME,
        I.INDEX_ID,
        (CASE IC.IS_INCLUDED_COLUMN
            WHEN 1 THEN IC.INDEX_COLUMN_ID
            ELSE IC.KEY_ORDINAL
         END)
           AS [ID],
        CLMNS.NAME AS COLUMN_NAME,
        CLMNS.COLUMN_ID AS COLUMN_POSITION,
        IC.IS_DESCENDING_KEY AS DESCEND , I.IS_UNIQUE
   FROM          SYS.TABLES AS TBL
              INNER JOIN
                 SYS.INDEXES AS I
              ON (I.OBJECT_ID = TBL.OBJECT_ID)
           INNER JOIN
              SYS.INDEX_COLUMNS AS IC
           ON (IC.COLUMN_ID > 0
               AND (   IC.KEY_ORDINAL > 0
                    OR IC.PARTITION_ORDINAL = 0
                    OR IC.IS_INCLUDED_COLUMN != 0))
              AND (IC.INDEX_ID = CAST (I.INDEX_ID AS INT)
                   AND IC.OBJECT_ID = I.OBJECT_ID)
        INNER JOIN
           SYS.COLUMNS AS CLMNS
        ON CLMNS.OBJECT_ID = IC.OBJECT_ID AND CLMNS.COLUMN_ID = IC.COLUMN_ID
  WHERE 1 = 1
 --(I.NAME='???')
 --AND (TBL.NAME=???'  )
 ORDER BY TBL.NAME , INDEX_ID ASC , [ID]

MS SQL TABLE LIST 取得現有 MS SQL 的資料表清單/欄位清單

'取得現有 MS SQL 的資料表清單 (含擴充屬性的描述)

SELECT o.name AS table_name,  ep.value  AS 說明 

  FROM    sys.objects O

       LEFT JOIN

          sys.extended_properties AS ep

       ON o.object_id = ep.major_id

 WHERE     1 = 1

       AND ISNULL (minor_id, 0) = 0

       AND O.name NOT LIKE 'sys%'

       AND O.type = 'U'

ORDER BY O.name

'取得現有 MS SQL 的資料表欄位清單

SELECT b.colorder,

       a.name AS table_name,

       b.name AS FNAME,

       (SELECT convert (VARCHAR, value)

          FROM ::

                  fn_listextendedproperty (NULL,

                                           'user',

                                           'dbo',

                                           'table',

                                           a.name,

                                           'column',

                                           b.name

               ))

          AS DESCRIPT,

       b.xtype,

       (SELECT    TOP 1

               c.name

          FROM systypes c

         WHERE c.xtype = b.xtype)

          AS ctype,

       b.length,

       b.isnullable,

       ''

  FROM sysobjects a, syscolumns b

 WHERE a.id = b.id AND A.type = 'U'

ORDER BY a.name, b.colorder