Skip to content

Instantly share code, notes, and snippets.

@ignu
Created February 15, 2010 17:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ignu/304794 to your computer and use it in GitHub Desktop.
Save ignu/304794 to your computer and use it in GitHub Desktop.
SELECT
RC.Constraint_Name AS FK_Constraint,
RC.Constraint_Catalog AS FK_Database,
CCU.Table_Name AS FK_Table,
CCU.Column_Name AS FK_Column,
RC.Unique_Constraint_Name AS PK_Constraint,
RC.Unique_Constraint_Catalog AS PK_Database,
RC.Unique_Constraint_Schema AS PK_Schema,
CCU2.Table_Name AS PK_Table,
CCU2.Column_Name AS PK_Column,
'CREATE INDEX [IX_' + CCU.Table_Name + '_' + CCU.Column_Name + '] ON [dbo].[' +
CCU.Table_Name + '] (' + CCU.Column_Name + ' ASC )' AS SQLScript
FROM
information_schema.referential_constraints RC JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
ORDER BY
RC.Constraint_NAME
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment