Queries to retrieve table information

Matt Pavey

If you do not have a third party tool available to build a list of table definitions for you, with field names, data types, sizes and so on, you can query syscolumns, sysobjects, and systypes directly to get the same information.

To get a list of all the tables:

SELECT o.name,
       'datecreated' = o.crdate
FROM   sysobjects o
WHERE  o.xtype  = 'U'
AND    o.status >= 0
ORDER BY o.name

To get the detailed definition for a specific table:

SELECT c.name,
       'type'     = t.name,
       'default'  = (SELECT column_default
                     FROM   information_schema.columns
                     WHERE  table_name  = o.name
                     AND    column_name = c.name),
       'length'   = CASE WHEN t.name LIKE '%char%' THEN c.prec ELSE c.length END,
       'null'     = c.isnullable,
       'identity' = SIGN(c.status & 128),
       'pk'       = (SELECT COUNT(1)
                     FROM   information_schema.table_constraints tc
                     JOIN   information_schema.key_column_usage kc
                            ON tc.constraint_name = kc.constraint_name
                     WHERE  tc.constraint_type = 'PRIMARY KEY'
                     AND    tc.table_name      = o.name
                     AND    kc.column_name     = c.name),
       'fk'       = (SELECT COUNT(1)
                     FROM   information_schema.table_constraints tc
                     JOIN   information_schema.key_column_usage kc
                            ON tc.constraint_name = kc.constraint_name
                     WHERE  tc.constraint_type = 'FOREIGN KEY'
                     AND    tc.table_name      = o.name
                     AND    kc.column_name     = c.name)
FROM   syscolumns c
JOIN   sysobjects o ON c.id = o.id
JOIN   systypes   t ON c.xusertype = t.xusertype
WHERE  o.xtype = 'U'
AND    o.name  = 'MyTable'
ORDER BY c.colorder

Besides the column name and data type, this gives you the default value, the size, whether the field allows nulls, whether it is an identity, and whether it takes part in the primary key or a foreign key.