SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 11:33 AM
| |||
| |||
 Re: How do i find indexes of the columns in SQL server 2000??? (001frien@gmail.com) writes: > How do i find indexes of the columns of all the tables of the > datbase...........most importantly in SQL server 2000 Here is a query. It lists only the five first index column, but you can easily augment it if needed. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx      |
|
03-01-2008, 11:34 AM
| |||
| |||
| Re: How do i find indexes of the columns in SQL server 2000??? Erland Sommarskog (esquel@sommarskog.se) writes: > Here is a query. It lists only the five first index column, but you can > easily augment it if needed. What was my INSERT-key up to last night? I missed to paste in the query I was talking about. Here it is: SELECT o.name, i.name, col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END), col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END), col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END), col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END), col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END) FROM sysobjects o JOIN sysindexes i ON i.id = o.id JOIN sysindexkeys ik ON ik.id = i.id AND ik.indid = i.indid JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid WHERE i.indid BETWEEN 1 AND 254 AND indexproperty(o.id, i.name, 'IsStatistics') = 0 AND indexproperty(o.id, i.name, 'IsHypothetical') = 0 GROUP BY o.name, i.name ORDER BY o.name, i.name -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
03-01-2008, 11:34 AM
| ||||
| ||||
| Re: How do i find indexes of the columns in SQL server 2000??? Thanks a lot Erland Erland Sommarskog wrote: > Erland Sommarskog (esquel@sommarskog.se) writes: > > Here is a query. It lists only the five first index column, but you can > > easily augment it if needed. > > What was my INSERT-key up to last night? I missed to paste in the > query I was talking about. Here it is: > > SELECT o.name, i.name, > col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END), > col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END), > col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END), > col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END), > col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END) > FROM sysobjects o > JOIN sysindexes i ON i.id = o.id > JOIN sysindexkeys ik ON ik.id = i.id > AND ik.indid = i.indid > JOIN syscolumns c ON c.id = ik.id > AND c.colid = ik.colid > WHERE i.indid BETWEEN 1 AND 254 > AND indexproperty(o.id, i.name, 'IsStatistics') = 0 > AND indexproperty(o.id, i.name, 'IsHypothetical') = 0 > GROUP BY o.name, i.name > ORDER BY o.name, i.name > > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/pro...ads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Linear Mode
