SQL Server Performance

Function that Retrieves All Foreign Key Columns th

Discussion in 'Contribute Your SQL Server Scripts' started by LearnSqlServer.com, Aug 25, 2006.

  1. LearnSqlServer.com New Member

    I was doing some performance-related data collection for a client today and thought I'd share one thing I wrote - a very cool script.

    Posted in my blog as well: http://www.learnsqlserver.com/Blogs...8/function-that-retrieves-all-foreign_24.html


    quote:set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[DBA_GetForeignKeyColumnsNotInAnyIndex] ()
    RETURNS TABLE
    AS
    /*
    Author: Scott Whigham fromhttp://www.LearnSqlServer.com/

    Description: This is a helpful script to run on a new database or even periodically. Since JOIN
    candidates can usually benefit from an index, this query identifies any ChildTable.ForeignKeyColumns
    that are not in any index whatsoever. It does not matter to this script whether or not the column
    is the leading, middle, or last column in the index. You could easily modify this to find foreign key columns
    that were not the leading column in an index, find those not in a clustered index, et al.
    Feel free to modify this at will!

    Misc Notes: I like this in the "model" database so that it will be propagated to any new databases on the server.
    We can't just dump it in "master" since INFORMATION_SCHEMA views do not change context.
    I wrote this as a function instead of a view because I may wish to add parameters to it later on.
    I prefer this to be a function instead of a proc so that I can write scripts like this:

    SELECT 'CREATE INDEX [nci_' + REPLACE(REPLACE(REPLACE(ChildTable, '[', ''), ']', ''), 'dbo.', '') + '_' + Column_Name + '] ON ' + ChildTable + ' ([' + Column_Name + '])'
    , 'DROP INDEX ' + ChildTable + '.[nci_' + REPLACE(REPLACE(ChildTable, '[', ''), ']', '') + '_' + Column_Name + ']'
    FROM DBA_GetForeignKeyColumnsNotInAnyIndex()

    Versions: SQL Server 2005+

    Executing this function:
    SELECT * FROM dbo.DBA_GetForeignKeyColumnsNotInAnyIndex ()
    ORDER BY ChildTable, COLUMN_NAME

    Creation Date: August 24, 2006

    For more scripts like this one, visithttp://www.LearnSqlServer.com/
    */
    RETURN (
    -- Foreign Key Columns
    SELECT OBJECT_ID ( tc.TABLE_SCHEMA + '.' + tc.TABLE_NAME ) AS ObjectId
    , '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']' AS ChildTable, cu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc -- Returns child table info
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
    ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

    EXCEPT

    -- Columns in Indexes
    SELECT DISTINCT c.object_id
    , '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']' AS TableIdentifier
    , c.name AS ForeignKeyColumnsNotInAnyIndex
    FROM sys.all_columns c JOIN sys.index_columns sc
    ON sc.object_id = c.object_id
    AND sc.column_id = c.column_id
    JOIN INFORMATION_SCHEMA.TABLES t
    ON sc.object_id = OBJECT_ID ( t.TABLE_SCHEMA + '.' + t.TABLE_NAME )
    )
    GO
    SELECT * FROM [DBA_GetForeignKeyColumnsNotInAnyIndex]()

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  2. FrankKalis Moderator

    What is this called? A shameless plug? <br />Anyway, useful script. Thanks for sharing it, Scott! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page