April 13, 2016 at 5:47 pm
Ok I need to show either table by table (if easier) or all tables in a db the following.
Name of Table. Name of Index.
Then flags for each index that indicate the following (1/0) for each of the following individually:
Index On PK (1/0)
Index On FK (1/0)
IsUnique (1/0) -- In other words is this index enforcing uniqueness?
Then what would also be helpful would be some sort of value; maybe an integer that would group index's on a table. So for example lets say that a table has two indexes. One with a single field so this numeric value might be say 1 for that column in returned results.
Now the second index has two columns as part of the index so this numeric value would have say 2 for each of those two records; obviously the same table and index name for each record but each column would have the column associated with that index.
If a table had an index with say 4 columns part of that index then this numeric value would be the same for each of those columns.
Can anyone help with this? Really it is only indexes that enforce unique values that is of use right now but a bit field would provide more flexibility as you would have a view of everything that way.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 13, 2016 at 7:29 pm
Figured out solution. Still need to fine tune it but if it is useful to anyone here it is.
You can change the DB name, I just have that going into a table so multiple DB's can be there. I will probably make it dynamic down the road.
;WITH CTE
AS (
SELECT 'Target_MergeAMD2_WithData' DBName, ic.[index_id] + ic.[object_id] AS [IndexId],t.[name] AS [TableName]
,i.[name] AS [IndexName],c.[name] AS [ColumnName],i.[type_desc]
,i.[is_primary_key],i.[is_unique]
FROM [sys].[indexes] i
INNER JOIN [sys].[index_columns] ic
ON i.[index_id] = ic.[index_id]
AND i.[object_id] = ic.[object_id]
INNER JOIN [sys].[columns] c
ON ic.[column_id] = c.[column_id]
AND i.[object_id] = c.[object_id]
INNER JOIN [sys].[tables] t
ON i.[object_id] = t.[object_id]
)
INSERT INTO [etl].[dbo].[ETLDBIndexes]
(DBName, TableName, IndexName, IndexType, isPrimaryKey, isUnique, IndexColumns)
SELECT DBName, c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
,STUFF( ( SELECT ','+ a.[ColumnName] FROM CTE a WHERE c.[IndexId] = a.[IndexId] FOR XML PATH('')),1 ,1, '') AS [Columns]
FROM CTE c
GROUP BY DBName, c.[IndexId],c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
ORDER BY DBName, c.[TableName] ASC,c.[is_primary_key] DESC;
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 13, 2016 at 9:10 pm
Jeffery Williams (4/13/2016)
Figured out solution. Still need to fine tune it but if it is useful to anyone here it is.You can change the DB name, I just have that going into a table so multiple DB's can be there. I will probably make it dynamic down the road.
;WITH CTE
AS (
SELECT 'Target_MergeAMD2_WithData' DBName, ic.[index_id] + ic.[object_id] AS [IndexId],t.[name] AS [TableName]
,i.[name] AS [IndexName],c.[name] AS [ColumnName],i.[type_desc]
,i.[is_primary_key],i.[is_unique]
FROM [sys].[indexes] i
INNER JOIN [sys].[index_columns] ic
ON i.[index_id] = ic.[index_id]
AND i.[object_id] = ic.[object_id]
INNER JOIN [sys].[columns] c
ON ic.[column_id] = c.[column_id]
AND i.[object_id] = c.[object_id]
INNER JOIN [sys].[tables] t
ON i.[object_id] = t.[object_id]
)
INSERT INTO [etl].[dbo].[ETLDBIndexes]
(DBName, TableName, IndexName, IndexType, isPrimaryKey, isUnique, IndexColumns)
SELECT DBName, c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
,STUFF( ( SELECT ','+ a.[ColumnName] FROM CTE a WHERE c.[IndexId] = a.[IndexId] FOR XML PATH('')),1 ,1, '') AS [Columns]
FROM CTE c
GROUP BY DBName, c.[IndexId],c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
ORDER BY DBName, c.[TableName] ASC,c.[is_primary_key] DESC;
You don't need CTE here.
It's actually your enemy in this case.
By using it like this you make SQL server repeatedly retrieve the same data set as many times as it's mentioned in query. And you use only small part of the data retrieved by every particular execution.
Split it into 2 parts.
For SELECT you need only data from sys.tables and sys.indexes.
So put them instead of FROM CTE.
sys.columns and sys.index_columns are needed for STUFF only.
So, put them there.
After it's done the query becomes mush shorter and easier to understand:
INSERT INTO [etl].[dbo].[ETLDBIndexes]
(DBName, TableName, IndexName, IndexType, isPrimaryKey, isUnique, IndexColumns)
SELECT DB_NAME(), t.[name] [TableName], i.[name] AS [IndexName],
i.[type_desc] [type_desc], i.[is_primary_key],i.[is_unique]
,STUFF( ( SELECT ','+ c.[Name]
FROM [sys].[columns] c INNER JOIN [sys].[index_columns] ic on ic.[column_id] = c.[column_id] AND i.[object_id] = c.[object_id]
WHERE i.[index_id] = ic.[index_id] AND i.[object_id] = ic.[object_id] FOR XML PATH('')),1 ,1, '') AS [Columns]
FROM [sys].[tables] t
INNER JOIN [sys].[indexes] i ON i.[object_id] = t.[object_id]
ORDER BY [TableName] ASC,[is_primary_key] DESC, i.index_id ASC;
_____________
Code for TallyGenerator
April 14, 2016 at 2:53 am
do you want included columns as well?
Also, not sure what the output is but .....
Doing all these calculations on unused or duplicate indexes is a waste.
So first find duplicate indexes, unused indexes, consolidate the remaining indexes.
Then, maybe display space used by each index?
That would be a real world value of what the index is costing and not an arbitrary weighted value.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply