I have lots of tables with foreign keys and some have an index while others have not.
All foreign keys are named FK_<name of the foreign key> with indexes named IX_<name of the foreign key>.
Are there some good practices, given the column cardinality of the foreign key, to create (or not) indexes ? Could this be scripted as T-SQL commands ?
It does not matter if they are created via a T-SQL Script or via the Designer. Your question is a little ambiguous, so I am unsure if you are also asking if it is okay to index all of the foreign keys. However, if you are, indexes should be created on columns that are referenced frequently in queries and you can do the following to improve performance:
Run the database tuning wizard which will supply a summary of improvements and recommend indexes.
Index all of the foreign keys and run the execution plan (To see if queries are performing faster or slower).
To create an index via T-SQL:
CREATE INDEX IX_INDEX_NAME
ON Table (FieldName);
To get a list of all Foreign keys:
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
To generate a script that applies indexes across all foreign keys you could do this:
SELECT 'CREATE INDEX [IX_' + f.name + '] ON ' + OBJECT_NAME(f.parent_object_id) + '(' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ')]'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id