Listing Columns, Keys and Indexes For Specific Table

Now I’m investigating Entity Framework and how it creates tables and other database objects. It’s surprising that if you have a reference (foreign key) in your model, EF creates an index in the referenced table during the migration process by default. So, if I have just 5 distinct referencing values with a roughly equal quantity of referenced records (in this case about 20% for each value), SQL Server will never use that index.
To put the creation of database objects under control, I need a simple script to see what objects are really created after migration. There are 3 queries in the script that list the following information:

  • columns, their data types and null/not null information
  • primary/foreign keys, and referencing table and columns
  • clustered/non-clustered indexes and columns

Leave a Reply

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