Queries to retrieve table information

If you don't have a 3rd party tool available to build a list of table definitions for you, such a field names, data-types, sizes, etc. you can query syscolumns, sysobjects, and systypes directly to get this information:

 

For example to get a list of all of the tables you could use:

 

select     o.name,

             'datecreated' = o.crdate
from       sysobjects o
where     o.xtype = 'U'
and        o.status >= 0
order by  o.name

 

Or to get the detailed table definition for a specific table you could use:

 

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 you can also get the default value, size, whether the field allows nulls, whether the field is an indentity value or not, whether the field is part of the primary key, and whether the field is part of a foreign key, etc.