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!

--USE DATABASE_NAME
SELECT obj.name [Table],
col.name [Attribute],
typ.name [DataType],
col.isnullable [Allow Nulls?],
CASE WHEN d.name 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 '-'
    ELSE g.name
END [Ref Table],
CASE WHEN h.value is null
    THEN '-'
    ELSE h.value
END [Description]
FROM sysobjects AS obj
JOIN syscolumns AS col ON obj.id = col.id
JOIN systypes AS typ ON col.xtype = typ.xtype
LEFT JOIN (SELECT  so.id,sc.colid,sc.name
      FROM    syscolumns sc
      JOIN sysobjects so ON so.id = sc.id
      JOIN sysindexkeys si ON so.id = si.id
                    AND sc.colid = si.colid
      WHERE si.indid = 1) d on obj.id = d.id and col.colid = d.colid
LEFT JOIN sys.foreign_key_columns AS e
    ON obj.id = 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 obj.id = h.major_id AND col.colid = h.minor_id
WHERE obj.type = 'U' ORDER BY obj.name

Leave a Reply

Your email address will not be published. Required fields are marked *

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