Sunday, October 14, 2012

The link between .NET Data Type and SQL Data Type

If anyone is wondering what is the correct data type to map between .NET and SQL this link would be a good reference. :)

http://msdn.microsoft.com/en-us/library/ms131092.aspx

Friday, October 12, 2012

Finding out Primary Key Foreign Key Relationship

I have came across this script on the internet which actually read system table to find out the primary key foreign key relationship between the tables. It is really useful for me as the database I am currently exploring had lots of PK_FK relationship. I hope by sharing this information it would also be useful to someone else who needed to find out

SELECT fk.name 'FK Name', tp.name 'Parent table', cp.name, cp.column_id, tr.name 'Refrenced table', cr.name, cr.column_id FROM sys.foreign_keys fk INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id ORDER BY tp.name, cp.column_id