实用T
下面的T-SQL可以生成索引在当前数据库的外键上, 可以帮助我们找回外键上丢失的索引，查看索引命名是否规范。
-- declare memory tableDECLARE @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 databaseINSERT 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 INTDECLARE @item_counter INTSET @loop_counter = ISNULL((SELECT COUNT(*) FROM @INDEX_TABLE),0)SET @item_counter = 1DECLARE @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_counterBEGIN -- 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 querySET @index_name = 'IX_' + @table_name + '_' + @column_nameSET @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 @queryUPDATE @INDEX_TABLE SET new_index_name = @index_nameWHERE primary_key = @item_counter SET @item_counter = @item_counter + 1END-- present all FKey's with new indexSELECT schema_name, table_name, column_name, new_index_nameFROM @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。
TAG:
