Help creating a Data Dictionary

[codemech]

I use the following query to create a data dictionary which returns the table name, column name, data type, field length and nullable option. I can't seem to figure out how to indicate if a field is set as a primary key. Note: The LIKE statement just return my (M)ain and
(L)ookup tables.

SELECT TOP 100 PERCENT o.name AS

, c.name AS [Column], t.name AS Type, c.length, c.isnullable
FROM dbo.sysobjects o
INNER JOIN dbo.syscolumns c ON o.id = c.id
INNER JOIN dbo.systypes t ON c.xtype = t.xtype
WHERE (o.name LIKE N'm_%') OR
(o.name LIKE N'l_%')
ORDER BY o.name, c.name

Thanks,
C

 

[markVII]

Try using INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
instead of system tables.