实用TSQL之生成当前索引数据库中的外键上

       下面的T-SQL可以生成索引在当前数据库的外键上, 可以帮助我们找回外键上丢失的索引，查看索引命名是否规范。

-- declare memory table
DECLARE @INDEX_TABLE TABLE(
    primary_key INT IDENTITY(1,1) NOT NULL, 
    schema_name NVARCHAR(100), 
    table_name NVARCHAR(100), 
    column_name NVARCHAR(100),
    new_index_name NVARCHAR(100)
)

-- feed memory table by all foreign key without index in database
INSERT INTO @INDEX_TABLE
    SELECT
        S.name as [Schema name],
        object_name(T.object_id) AS [Table name],
        C.name AS [Column name],
        ''
    FROM 
        sys.columns C
        LEFT JOIN sys.tables T ON (C.object_id = T.object_id)
        LEFT JOIN sys.schemas S ON (S.schema_id = T.schema_id)
        LEFT JOIN sys.foreign_key_columns FKC ON (FKC.parent_object_id = C.object_id AND C.column_id = FKC.parent_column_id)
        LEFT JOIN sys.foreign_keys FK ON (FKC.constraint_object_id = FK.object_id)

        LEFT JOIN sys.index_columns IC ON (IC.object_id = C.object_id AND IC.column_id = C.column_id)    
        LEFT JOIN sys.indexes I ON (I.index_id = IC.index_id  AND I.object_id = C.object_id)
    WHERE 
        T.object_id is not null
        AND FK.name IS NOT NULL
        AND I.name IS NULL
    ORDER BY 
        S.name,
        object_name(T.object_id),
        C.name 


DECLARE @loop_counter INT
DECLARE @item_counter INT

SET @loop_counter = ISNULL((SELECT COUNT(*) FROM @INDEX_TABLE),0)
SET @item_counter = 1

DECLARE @schema_name VARCHAR(100)
DECLARE @table_name VARCHAR(100)
DECLARE @column_name VARCHAR(100)
DECLARE @query NVARCHAR(1000)
DECLARE @index_name VARCHAR(200)

WHILE @loop_counter > 0 AND @item_counter <= @loop_counter
BEGIN
        
    -- get one row from memory table
        SELECT 
            @schema_name = schema_name, 
            @table_name = table_name,
            @column_name = column_name
        FROM 
            @INDEX_TABLE
        WHERE 
            primary_key = @item_counter

    
-- prepare query
SET @index_name = 'IX_' + @table_name + '_' + @column_name
SET @query = 'CREATE NONCLUSTERED INDEX [' + @index_name + '] ON ['+ @schema_name+ '].[' + @table_name + '] 
    (
        [' + @column_name + '] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    '

EXEC sp_executesql @query

UPDATE @INDEX_TABLE 
SET 
    new_index_name = @index_name
WHERE 
    primary_key = @item_counter
    SET @item_counter =  @item_counter + 1
END

-- present all FKey's with new index
SELECT 
    schema_name, 
    table_name, 
    column_name,
    new_index_name
FROM 
    @INDEX_TABLE

---

以AdvantureWorks数据库为例，执行后返回

HumanResources	Employee	ContactID	IX_Employee_ContactID
Person	StateProvince	TerritoryID	IX_StateProvince_TerritoryID
Production	Product	ProductModelID	IX_Product_ProductModelID
Production	Product	ProductSubcategoryID	IX_Product_ProductSubcategoryID
Production	Product	SizeUnitMeasureCode	IX_Product_SizeUnitMeasureCode
Production	Product	WeightUnitMeasureCode	IX_Product_WeightUnitMeasureCode
Production	ProductSubcategory	ProductCategoryID	IX_ProductSubcategory_ProductCategoryID
Production	WorkOrderRouting	LocationID	IX_WorkOrderRouting_LocationID
Purchasing	PurchaseOrderHeader	ShipMethodID	IX_PurchaseOrderHeader_ShipMethodID
Purchasing	VendorAddress	AddressTypeID	IX_VendorAddress_AddressTypeID
Sales	CustomerAddress	AddressTypeID	IX_CustomerAddress_AddressTypeID
Sales	Individual	ContactID	IX_Individual_ContactID
Sales	SalesOrderDetail	SpecialOfferID	IX_SalesOrderDetail_SpecialOfferID
Sales	SalesOrderHeader	BillToAddressID	IX_SalesOrderHeader_BillToAddressID
Sales	SalesOrderHeader	ContactID	IX_SalesOrderHeader_ContactID
Sales	SalesOrderHeader	CreditCardID	IX_SalesOrderHeader_CreditCardID
Sales	SalesOrderHeader	CurrencyRateID	IX_SalesOrderHeader_CurrencyRateID
Sales	SalesOrderHeader	ShipMethodID	IX_SalesOrderHeader_ShipMethodID
Sales	SalesOrderHeader	ShipToAddressID	IX_SalesOrderHeader_ShipToAddressID
Sales	SalesOrderHeader	TerritoryID	IX_SalesOrderHeader_TerritoryID
Sales	SalesPerson	TerritoryID	IX_SalesPerson_TerritoryID

希望对您开发有帮助，您可以感兴趣的文章：

几个有用的T-SQL(1)
列出Server上5张最大的表

作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。