August 22, 2016 at 4:49 am
Hi,
I have an index and I am trying to find out which table and database it sits in.
How can I do this without trawling through all of the indexes of a table?
Thanks.
August 22, 2016 at 4:58 am
Join sys.indexes to sys.objects. You'll need to do that in every database (or use sp_MSForEachDB), though, if you don't know what database the index is in.
John
August 22, 2016 at 5:19 am
But that would give me all of the indexes for all tables in that database?
I would have to go through each and every index in each table to get to the one I am looking for.
Is there a way I can specify the name of the index and it brings back the table and databases?
August 22, 2016 at 5:22 am
navtec (8/22/2016)
But that would give me all of the indexes for all tables in that database?
Yes.
You'll still have to run it in each database, the schema-views are per-database, not per-server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2016 at 5:50 am
Ok thanks.
Also in addition to this, is there a way of finding out which index columns are of a certain type?
So for example to bring back all index columns for all tables in a database which are of type varchar.
August 22, 2016 at 5:54 am
Yes. Join to sys.index_columns, sys.indexes and sys.types.
John
August 22, 2016 at 5:56 am
will give it a try, thanks.
August 23, 2016 at 12:36 am
This will list all the indexes in all the tables in your DB.
Modify the WHERE clause to filter the results.
SELECT
databaseName = DB_NAME()
, Table_id = si.object_id
, SchemaName = s.name
, TableName = t.name
, Index_id = si.index_id
, idxName = si.name
, idxType = si.type_desc
, isPK = si.is_primary_key
, isUNQ = si.is_unique
, si.is_disabled
, si.has_filter
, [Columns] = LTRIM(RTRIM(
STUFF(
( SELECT ', ' + ac.name + ' [' + UPPER(dt.name)
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, ac.scale) + ')'
WHEN dt.name IN ('char', 'varchar')
THEN '(' + CASE WHEN ac.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, ac.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN ac.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, ac.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, ac.[precision]) + ', ' + CONVERT(VARCHAR, ac.scale) + ')'
ELSE '' END + ']'
+ CASE WHEN sic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns sic
INNER JOIN sys.all_columns ac
ON ac.object_id = t.object_id
AND ac.column_id = sic.column_id
INNER JOIN sys.types dt
ON dt.user_type_id = ac.user_type_id
WHERE sic.object_id = si.object_id
AND sic.index_id = si.index_id
AND sic.is_included_column = 0
ORDER BY sic.key_ordinal
FOR XML PATH('')
)
, 1, 2, '')
))
, Includes = LTRIM(RTRIM(
STUFF(
( SELECT ', ' + ac.name + ' [' + UPPER(dt.name)
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, ac.scale) + ')'
WHEN dt.name IN ('char', 'varchar')
THEN '(' + CASE WHEN ac.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, ac.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN ac.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, ac.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, ac.[precision]) + ', ' + CONVERT(VARCHAR, ac.scale) + ')'
ELSE '' END + ']'
FROM sys.index_columns sic
INNER JOIN sys.all_columns ac
ON ac.object_id = t.object_id
AND ac.column_id = sic.column_id
INNER JOIN sys.types dt
ON dt.user_type_id = ac.user_type_id
WHERE sic.object_id = si.object_id
AND sic.index_id = si.index_id
AND sic.is_included_column = 1
ORDER BY sic.key_ordinal
FOR XML PATH('')
)
, 1, 2, '')
))
, si.filter_definition
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
INNER JOIN sys.indexes si ON si.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS ius ON si.object_id = ius.object_id AND si.index_id = ius.index_id
WHERE t.type = 'U' -- USER_TABLE
AND si.type_desc != 'HEAP'
--AND s.name = 'SchemaName'
--AND t.name = 'TableName'
--AND si.name = 'IndexName'
--AND EXISTS (SELECT 1 FROM sys.index_columns sic
-- INNER JOIN sys.all_columns ac
-- ON ac.object_id = t.object_id
-- AND ac.column_id = sic.column_id
-- INNER JOIN sys.types dt
-- ON dt.user_type_id = ac.user_type_id
-- WHERE sic.object_id = si.object_id
-- AND sic.index_id = si.index_id
-- AND dt.name LIKE '%char%')
GROUP BY s.name, t.name, si.name, si.type_desc, si.is_primary_key, si.is_unique, si.is_disabled, t.object_id, si.object_id, si.index_id, si.has_filter, si.filter_definition
ORDER BY s.name, t.name, si.is_primary_key DESC, si.name;
August 23, 2016 at 9:18 am
Thats great, thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply