Hier ein kleines SQL-Script welches zu jeder Tabelle folgende Informationen ausgibt:
- Feldname
- Datentyp
- nullable
- ist das Feld ein primary key
- ist das Feld ein foreign key
- auf welche Tabelle verweist der foreign key
SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length,
[is nullable]=syscolumns.isnullable,
PK=(SELECT count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'Primary Key'
and tc.table_name=sysobjects.name
and ccu.column_name=syscolumns.name),
FK=(select count(*)
from sysforeignkeys s
inner join syscolumns c1 on ( s.fkeyid = c1.id and s.fkey = c1.colid )
inner join syscolumns c2 on ( s.rkeyid = c2.id and s.rkey = c2.colid )
where object_name(fkeyid)=sysobjects.name
and c1.name=syscolumns.name),
FK_on_table=(select top 1 object_name(rkeyid)
from sysforeignkeys s
inner join syscolumns c1 on ( s.fkeyid = c1.id and s.fkey = c1.colid )
inner join syscolumns c2 on ( s.rkeyid = c2.id and s.rkey = c2.colid )
where object_name(fkeyid)=sysobjects.name
and c1.name=syscolumns.name
and c2.name=syscolumns.name)
FROM
sysobjects
JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE
sysobjects.[xtype]='u'
ORDER BY
sysobjects.[name],
syscolumns.colid