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