|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
report on indexes on foreign keys
Hi,
I have a database with over 100 tables. About 60 of them starts with tblssl_. I need to look at every table, find out if it has foreign keys and figure out if that key has an index on it. That foreign key might be a part of a composite index. I need to know they key name, index name (if any), whether it is clustered or non-clustered, and to have nothing next to the foreign key if no index is defined on it. Is there a way I can run a query against DMVs to get that information or the only way I can do this is by looking at each individual table? T. T.
Look at the following DMVs. You should be able to use these to answer your questions. sys.foreign_keys sys.foreign_key_columns sys.indexes sys.index_columns Of course, every foreign key may not benefit from an index. That is up to you to determine. RLF Show quoteHide quote "tolcis" <nytolly***@gmail.com> wrote in message news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > Hi, > > I have a database with over 100 tables. About 60 of them starts with > tblssl_. I need to look at every table, find out if it has foreign > keys and figure out if that key has an index on it. That foreign key > might be a part of a composite index. > I need to know they key name, index name (if any), whether it is > clustered or non-clustered, and to have nothing next to the foreign > key if no index is defined on it. > Is there a way I can run a query against DMVs to get that information > or the only way I can do this is by looking at each individual table? > > T.
Show quote
Hide quote
On Jul 8, 3:55 pm, "Russell Fields" <russellfie***@nomail.com> wrote: Thank you,> T. > > Look at the following DMVs. You should be able to use these to answer your > questions. > sys.foreign_keys > sys.foreign_key_columns > sys.indexes > sys.index_columns > > Of course, every foreign key may not benefit from an index. That is up to > you to determine. > > RLF > > "tolcis" <nytolly***@gmail.com> wrote in message > > news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > > > Hi, > > > I have a database with over 100 tables. About 60 of them starts with > > tblssl_. I need to look at every table, find out if it has foreign > > keys and figure out if that key has an index on it. That foreign key > > might be a part of a composite index. > > I need to know they key name, index name (if any), whether it is > > clustered or non-clustered, and to have nothing next to the foreign > > key if no index is defined on it. > > Is there a way I can run a query against DMVs to get that information > > or the only way I can do this is by looking at each individual table? > > > T. but I am not sure how to join all of them to get the report that I need. Object_id is especially confusing. T. tolcis (nytolly***@gmail.com) writes:
>> Look at the following DMVs. You should be able to use these to answer All constraints are objects in their own right, so >> your questions. >> sys.foreign_keys >> sys.foreign_key_columns >> sys.indexes >> sys.index_columns >> > but I am not sure how to join all of them to get the report that I > need. Object_id is especially confusing. sys.foreign_keys.object_id is the id for the FK constraint. Then the column parent_object_id is the table the FK constraint belongs to, while referenced_object_id is the table the FK constraint refers to. Indexes on the other hand are not objects on their on, but are are identified by (object_id, index_id), where the index_id is unique only within the object. Same goes for columns. A column is identified by (object_id, column_id) where column_id is a running number within the table. Yes, it will take some time to come with the correct query, but it is a good exercise, and it will not be last metadata query you right, I promise! -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx tolcis
I modified Aaron's query a little bit, see if that helps you WITH fk_no_indexes AS ( SELECT [constraint_name] = f.[name], [child_table] = OBJECT_NAME(f.parent_object_id), [child_table_id]=f.parent_object_id, [child_column] = cc.name, [child_column_id]=cc.[column_id], [parent_table] = OBJECT_NAME(f.referenced_object_id), [parent_column] = pc.name FROM sys.foreign_keys f INNER JOIN ( SELECT c.[object_id], c.name, c.column_id, ic.index_id FROM sys.columns c INNER JOIN sys.index_columns ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id ) AS pc ON f.key_index_id = pc.index_id INNER JOIN sys.foreign_key_columns fkc ON f.[object_id] = fkc.constraint_object_id AND pc.[object_id] = fkc.referenced_object_id AND fkc.referenced_column_id = pc.column_id INNER JOIN sys.columns cc ON fkc.parent_object_id = cc.[object_id] AND fkc.parent_column_id = cc.column_id ) SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM fk_no_indexes WHERE NOT EXISTS (SELECT * FROM sys.index_columns i WHERE i.[object_id]=[child_table_id] AND [child_column_id]=column_id ) ORDER BY constraint_name, child_table; Show quoteHide quote "tolcis" <nytolly***@gmail.com> wrote in message news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > Hi, > > I have a database with over 100 tables. About 60 of them starts with > tblssl_. I need to look at every table, find out if it has foreign > keys and figure out if that key has an index on it. That foreign key > might be a part of a composite index. > I need to know they key name, index name (if any), whether it is > clustered or non-clustered, and to have nothing next to the foreign > key if no index is defined on it. > Is there a way I can run a query against DMVs to get that information > or the only way I can do this is by looking at each individual table? > > T.
Show quote
Hide quote
On Jul 9, 1:55 am, "Uri Dimant" <u***@iscar.co.il> wrote: Hi Uri,> tolcis > I modified Aaron's query a little bit, see if that helps you > WITH fk_no_indexes > > AS > > ( > > SELECT > > [constraint_name] = f.[name], > > [child_table] = OBJECT_NAME(f.parent_object_id), > > [child_table_id]=f.parent_object_id, > > [child_column] = cc.name, > > [child_column_id]=cc.[column_id], > > [parent_table] = OBJECT_NAME(f.referenced_object_id), > > [parent_column] = pc.name > > FROM > > sys.foreign_keys f > > INNER JOIN > > ( > > SELECT > > c.[object_id], > > c.name, > > c.column_id, > > ic.index_id > > FROM > > sys.columns c > > INNER JOIN > > sys.index_columns ic > > ON > > c.[object_id] = ic.[object_id] > > AND c.column_id = ic.column_id > > ) AS pc > > ON > > f.key_index_id = pc.index_id > > INNER JOIN > > sys.foreign_key_columns fkc > > ON > > f.[object_id] = fkc.constraint_object_id > > AND pc.[object_id] = fkc.referenced_object_id > > AND fkc.referenced_column_id = pc.column_id > > INNER JOIN > > sys.columns cc > > ON > > fkc.parent_object_id = cc.[object_id] > > AND fkc.parent_column_id = cc.column_id > > ) > > SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM > fk_no_indexes WHERE NOT EXISTS > > (SELECT * FROM sys.index_columns i > > WHERE i.[object_id]=[child_table_id] > > AND [child_column_id]=column_id > > ) > > ORDER BY > > constraint_name, > > child_table; > > "tolcis" <nytolly***@gmail.com> wrote in message > > news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > > > Hi, > > > I have a database with over 100 tables. About 60 of them starts with > > tblssl_. I need to look at every table, find out if it has foreign > > keys and figure out if that key has an index on it. That foreign key > > might be a part of a composite index. > > I need to know they key name, index name (if any), whether it is > > clustered or non-clustered, and to have nothing next to the foreign > > key if no index is defined on it. > > Is there a way I can run a query against DMVs to get that information > > or the only way I can do this is by looking at each individual table? > > > T. Thank you, that works but I don't see indexes names next to each Foreign Key. Also, I need to know all foreign keys that don't have any indexes. Thanks, T. Have a look into sys.index_columns table
>Also, I need to know all foreign keys that don't have Can you tell me (post sample data) where that did nnot work?>any indexes. Show quoteHide quote "tolcis" <nytolly***@gmail.com> wrote in message Hi Uri,news:fdc1538f-c946-4089-b801-89e51667f4ec@o6g2000yqj.googlegroups.com... On Jul 9, 1:55 am, "Uri Dimant" <u***@iscar.co.il> wrote: > tolcis > I modified Aaron's query a little bit, see if that helps you > WITH fk_no_indexes > > AS > > ( > > SELECT > > [constraint_name] = f.[name], > > [child_table] = OBJECT_NAME(f.parent_object_id), > > [child_table_id]=f.parent_object_id, > > [child_column] = cc.name, > > [child_column_id]=cc.[column_id], > > [parent_table] = OBJECT_NAME(f.referenced_object_id), > > [parent_column] = pc.name > > FROM > > sys.foreign_keys f > > INNER JOIN > > ( > > SELECT > > c.[object_id], > > c.name, > > c.column_id, > > ic.index_id > > FROM > > sys.columns c > > INNER JOIN > > sys.index_columns ic > > ON > > c.[object_id] = ic.[object_id] > > AND c.column_id = ic.column_id > > ) AS pc > > ON > > f.key_index_id = pc.index_id > > INNER JOIN > > sys.foreign_key_columns fkc > > ON > > f.[object_id] = fkc.constraint_object_id > > AND pc.[object_id] = fkc.referenced_object_id > > AND fkc.referenced_column_id = pc.column_id > > INNER JOIN > > sys.columns cc > > ON > > fkc.parent_object_id = cc.[object_id] > > AND fkc.parent_column_id = cc.column_id > > ) > > SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM > fk_no_indexes WHERE NOT EXISTS > > (SELECT * FROM sys.index_columns i > > WHERE i.[object_id]=[child_table_id] > > AND [child_column_id]=column_id > > ) > > ORDER BY > > constraint_name, > > child_table; > > "tolcis" <nytolly***@gmail.com> wrote in message > > news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > > > Hi, > > > I have a database with over 100 tables. About 60 of them starts with > > tblssl_. I need to look at every table, find out if it has foreign > > keys and figure out if that key has an index on it. That foreign key > > might be a part of a composite index. > > I need to know they key name, index name (if any), whether it is > > clustered or non-clustered, and to have nothing next to the foreign > > key if no index is defined on it. > > Is there a way I can run a query against DMVs to get that information > > or the only way I can do this is by looking at each individual table? > > > T. Thank you, that works but I don't see indexes names next to each Foreign Key. Also, I need to know all foreign keys that don't have any indexes. Thanks, T. search the web and you can find a script that will not only identify foreign
keys without indexes, but also generate the index creation scripts for you. -- Show quoteHide quoteKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "tolcis" <nytolly***@gmail.com> wrote in message news:8f519ebe-38a1-4b28-b432-13c290300da3@37g2000yqp.googlegroups.com... > Hi, > > I have a database with over 100 tables. About 60 of them starts with > tblssl_. I need to look at every table, find out if it has foreign > keys and figure out if that key has an index on it. That foreign key > might be a part of a composite index. > I need to know they key name, index name (if any), whether it is > clustered or non-clustered, and to have nothing next to the foreign > key if no index is defined on it. > Is there a way I can run a query against DMVs to get that information > or the only way I can do this is by looking at each individual table? > > T.  
Other interesting topics
SQL cannot connect to database for OnePoint/MOM/ForeFront all of the sudden???
using profiler to log master.sysprocesses entries ? Best Practices - using params passed to stored procedure Locks and SQLAgent - Generic refresher, Alert Engine Connection server utilizing trusted connection to other SQL Server RESTORING drop table not showing up in the transaction log SQL 2k and autonumbering Exception Access Violation in SQL SQl Server Reporting Services - migrating 2000 to 2008 |
|||||||||||||||||||||||
