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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy