SQL 2000: Query Table Information

-- SQL Server 2000
-- Query table information

DECLARE @object_name SYSNAME
SET @object_name = 'mytable'

SELECT DISTINCT
    CASE o.type
     WHEN 'C'  THEN 'CHECK constraint'
     WHEN 'D'  THEN 'Default or DEFAULT constraint'
     WHEN 'F'  THEN 'FOREIGN KEY constraint'
     WHEN 'FN' THEN 'Scalar function'
     WHEN 'IF' THEN 'Inlined table-function'
     WHEN 'K'  THEN 'PRIMARY KEY or UNIQUE constraint'
     WHEN 'L'  THEN 'Log'
     WHEN 'P'  THEN 'Stored procedure'
     WHEN 'R'  THEN 'Rule'
     WHEN 'RF' THEN 'Replication filter stored procedure'
     WHEN 'S'  THEN 'System table'
     WHEN 'TF' THEN 'Table function'
     WHEN 'TR' THEN 'Trigger'
     WHEN 'U'  THEN 'User table'
     WHEN 'V'  THEN 'View'
     WHEN 'X'  THEN 'Extended stored procedure'
    END 'object type'
  , o.name 'object name'
  , c.name 'col name'
  , t.name type
  , c.length
  , c.xprec 'precision'
  , c.xscale scale
  , CASE
     WHEN c.isnullable = 0 THEN 'No'
     ELSE 'Yes'
    END nullable
  , CASE
     WHEN p.value IS NULL THEN ''
     ELSE p.value
    END 'description'
  , (
   SELECT CASE WHEN COUNT(*) > 0 THEN 'Yes' ELSE '' END
     FROM sysindexes i
       INNER JOIN sysindexkeys k ON i.id = k.id
              AND k.indid = i.indid
              AND k.colid = c.colid
     WHERE i.id = o.id
       AND i.indid > 0 AND i.indid < 255 -- indid(0) heap; indid(255) text or rimage
       AND i.status & 64 = 0  -- leave out AUTO_STATISTICS; see master.dbo.spt_values.type = I for details
    ) 'indexed'
  , CASE
     WHEN q.value IS NULL THEN ''
     ELSE q.value
    END 'object description'
  , c.colid 'col'

 FROM sysobjects o
   LEFT JOIN syscolumns c ON o.id = c.id
   LEFT JOIN systypes t   ON c.xtype = t.xtype
   -- col description
   LEFT JOIN sysproperties p ON c.id  = p.id
          AND c.colid = p.smallid
   -- object description
   LEFT JOIN sysproperties q ON o.id = q.id
          AND q.smallid = 0

 WHERE o.name = @object_name
    OR c.name = @object_name

 ORDER BY c.colid
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s