How To Find All Tables, Columns, Data Types of SQL Server Database

Recently I had to find all the tables, columns, data types, etc. from a database. Below is a thorough script that brings back all the tables, attributes, data types, whether the column allows NULLS, whether it’s a Primary Key, or a Foreign Key (and if so, the referencing table). It’s extremely useful and easy to run.

Here is the script. Just make sure to un-comment the first line and replace it with USE yourDB!

SELECT [Table], [Attribute], [DataType],
col.isnullable [Allow Nulls?],
CASE WHEN is null
    THEN 0
    ELSE 1
END [PKey?],
CASE WHEN e.parent_object_id is null
    THEN 0
    ELSE 1
END [FKey?],
CASE WHEN e.parent_object_id is null
    THEN '-'
END [Ref Table],
CASE WHEN h.value is null
    THEN '-'
    ELSE h.value
END [Description]
FROM sysobjects AS obj
JOIN syscolumns AS col ON =
JOIN systypes AS typ ON col.xtype = typ.xtype
      FROM    syscolumns sc
      JOIN sysobjects so ON =
      JOIN sysindexkeys si ON =
                    AND sc.colid = si.colid
      WHERE si.indid = 1) d on = and col.colid = d.colid
LEFT JOIN sys.foreign_key_columns AS e
    ON = e.parent_object_id AND col.colid = e.parent_column_id   
LEFT JOIN sys.objects as g
    ON e.referenced_object_id = g.object_id 
LEFT JOIN sys.extended_properties AS h
    ON = h.major_id AND col.colid = h.minor_id
WHERE obj.type = 'U' ORDER BY

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.