April 11, 2018 at 6:41 am
Hi all
I need to get all the indexes from all tables in all databases including:-
Database name
Table name
Index name
Index Columns
Included columns
I've currently got the following code which gives me the above but only works one database at a time (and doesn't give me the database name):- WITH idxs AS
(
SELECT
TableName = t.[name]
,IndexName = i.[name]
,IndexColumns = DS1.[IndexColumnsNames]
,IncludeColumns = DS2.[IncludedColumnsNames]
FROM
[sys].[indexes] i
INNER JOIN [sys].[tables] t
ON i.[object_id] = t.[object_id]
AND t.is_ms_shipped = 0
AND i.name LIKE 'idx%'
CROSS APPLY
(
SELECT
STUFF((
SELECT
' ' + c.[name] + ','
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c
ON ic.[object_id] = c.[object_id]
AND ic.[column_id] = c.[column_id]
WHERE
i.[object_id] = ic.[object_id]
AND i.[index_id] = ic.[index_id]
AND ic.[is_included_column] = 0
FOR XML PATH('')
)
,1
,1
,''
)
)DS1([IndexColumnsNames])
CROSS APPLY
(
SELECT
STUFF((
SELECT
' ' + c.[name] + ','
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c
ON ic.[object_id] = c.[object_id]
AND ic.[column_id] = c.[column_id]
WHERE
i.[object_id] = ic.[object_id]
AND i.[index_id] = ic.[index_id]
AND ic.[is_included_column] = 1
FOR XML PATH('')
)
,1
,1
,''
)
)DS2([IncludedColumnsNames])
)
,idxoutput AS
(
SELECT
TableName = TableName
,IndexName = IndexName
,IndexColumns = LEFT(IndexColumns, LEN(IndexColumns) - 1)
,IncludeColumns = LEFT(IncludeColumns, LEN(IncludeColumns) - 1)
FROM
idxs
)
SELECT
*
FROM
idxoutput
I need to convert the above so it will run across all databases and also show mt which database it's currently looking at.
I've been looking into use sp_MSforeachdb to loop through all the databases but I've run into some issues.
This is my latest attempt:-EXEC sp_MSforeachdb 'select "?" AS dbName, * from (
SELECT
TableName = t.[name]
,IndexName = i.[name]
,IndexColumns = DS1.[IndexColumnsNames]
,IncludeColumns = DS2.[IncludedColumnsNames]
FROM
[sys].[indexes] i
INNER JOIN [sys].[tables] t
ON i.[object_id] = t.[object_id]
AND t.is_ms_shipped = 0
AND i.name LIKE ''idx%''
CROSS APPLY
(
SELECT
STUFF((
SELECT
c.[name] + ''','''
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c
ON ic.[object_id] = c.[object_id]
AND ic.[column_id] = c.[column_id]
WHERE
i.[object_id] = ic.[object_id]
AND i.[index_id] = ic.[index_id]
AND ic.[is_included_column] = 0
FOR XML PATH('')
)
,1
,1
,''''
)
)DS1([IndexColumnsNames])
CROSS APPLY
(
SELECT
STUFF((
SELECT
c.[name] + ''','''
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c
ON ic.[object_id] = c.[object_id]
AND ic.[column_id] = c.[column_id]
WHERE
i.[object_id] = ic.[object_id]
AND i.[index_id] = ic.[index_id]
AND ic.[is_included_column] = 1
FOR XML PATH('')
)
,1
,1
,''''
)
)DS2([IncludedColumnsNames])
)
'
but I'm getting errors as follows:-
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'master'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'tempdb'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
There is the same set of errors for each database on this instance.
Can anyone help me with this one?
April 11, 2018 at 7:04 am
The command argument for sp_MSforeachdb has a max length of 2,000 characters, and your query exceeds that.
There are alternatives to sp_MSforeachdb out there which work better – here is an example.
The 'current' database name can be obtained using DB_NAME().
If you first put your code into a variable as follows, you should be able to use that as the parameter:
DECLARE @SQL VARCHAR(5000) = '
WITH idxs
AS
(
SELECT
TableName = t.name
, IndexName = i.name
, IndexColumns = DS1.IndexColumnsNames
, IncludeColumns = DS2.IncludedColumnsNames
FROM
sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
AND t.is_ms_shipped = 0
AND i.name LIKE ''idx%''
CROSS APPLY
(
SELECT STUFF((
SELECT '' '' + c.name + '',''
FROM
sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE
i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 0
FOR XML PATH('''')
)
,1
,1
,''''
)
) DS1(IndexColumnsNames)
CROSS APPLY
(
SELECT STUFF((
SELECT '' '' + c.name + '',''
FROM
sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE
i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
FOR XML PATH('''')
)
,1
,1
,''''
)
) DS2(IncludedColumnsNames)
)
, idxoutput
AS
(
SELECT
TableName = idxs.TableName
, IndexName = idxs.IndexName
, IndexColumns = LEFT(idxs.IndexColumns, LEN(idxs.IndexColumns) - 1)
, IncludeColumns = LEFT(idxs.IncludeColumns, LEN(idxs.IncludeColumns) - 1)
FROM idxs
)
SELECT *
FROM idxoutput;'
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2018 at 2:34 pm
Why do you need to save it in a string format? Could you just grab the meta-data instead, and leave it a table format until/unless you actually needed to convert it to a character string?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 17, 2018 at 8:17 am
The current plan is to store the current indexes in a table.
The thinking is that we could add a record to the table (in the right format) and run a script every night to see if anything has been added to the table and add/adjust/delete an index automatically.
Our holding table looks like this:-CREATE TABLE [dbo].[tbl_Index_Control](
[DataBaseName] [varchar](500) NOT NULL,
[TableName] [varchar](500) NOT NULL,
[IndexName] AS ((('idx_'+[TableName])+'_')+replace([IndexColumns],',','_')),
[IndexColumns] [varchar](500) NOT NULL,
[IncludeColumns] [varchar](500) NULL
) ON [PRIMARY]
GO
We have a stored procedure that's being worked on to read this table, compare it with current indexes and do whatever is necessary.
However, we need to load the table in the first place which is where I'm coming a bit unstuck.
April 17, 2018 at 8:38 am
richardmgreen1 - Tuesday, April 17, 2018 8:17 AMThe current plan is to store the current indexes in a table.
The thinking is that we could add a record to the table (in the right format) and run a script every night to see if anything has been added to the table and add/adjust/delete an index automatically.Our holding table looks like this:-
CREATE TABLE [dbo].[tbl_Index_Control](
[DataBaseName] [varchar](500) NOT NULL,
[TableName] [varchar](500) NOT NULL,
[IndexName] AS ((('idx_'+[TableName])+'_')+replace([IndexColumns],',','_')),
[IndexColumns] [varchar](500) NOT NULL,
[IncludeColumns] [varchar](500) NULL
) ON [PRIMARY]
GOWe have a stored procedure that's being worked on to read this table, compare it with current indexes and do whatever is necessary.
However, we need to load the table in the first place which is where I'm coming a bit unstuck.
So are you saying that my suggestions did not resolve your issues? Can you explain why?
Also, I think that I am right in saying that there is no known good way of automating the creation, modification and removal of SQL Server indexes, especially if you are looking only at table structures and not at historical performance and usage.
I do not understand how comparing what is in the table with 'current indexes' will ever find any differences, given that the table contains current definitions. I must be misunderstanding what you are trying to say.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 17, 2018 at 8:51 am
Hi Phil
Apologies, I've been away from work and not had chance to work on your suggestion.
I was responding to Scott's post with the purpose of my question.
The idea is to preload the table once with the current indexes (hence my original question and your answer).
After that, the table will simply be used in one of three ways:-
1) Add a record to create a new index
2) Alter a record to change an index
3) Delete a record in order to remove an index that is no longer needed
April 17, 2018 at 8:58 am
richardmgreen1 - Tuesday, April 17, 2018 8:51 AMHi PhilApologies, I've been away from work and not had chance to work on your suggestion.
I was responding to Scott's post with the purpose of my question.
The idea is to preload the table once with the current indexes (hence my original question and your answer).
After that, the table will simply be used in one of three ways:-
1) Add a record to create a new index
2) Alter a record to change an index
3) Delete a record in order to remove an index that is no longer needed
Thanks for the explanation. That's a slightly unusual way of working, but I'm sure you have your reasons.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 17, 2018 at 9:10 am
I still don't understand why you need the index in string format to determine that. It's actually easier to accurately compare the meta-data rather than a string definition (where noncritical things like fillfactor and ON DELETE CASCADE would cause index diffs to be seen even when all the other things in the index are still identical).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 17, 2018 at 9:16 am
Phil Parkin - Tuesday, April 17, 2018 8:58 AMrichardmgreen1 - Tuesday, April 17, 2018 8:51 AMHi PhilApologies, I've been away from work and not had chance to work on your suggestion.
I was responding to Scott's post with the purpose of my question.
The idea is to preload the table once with the current indexes (hence my original question and your answer).
After that, the table will simply be used in one of three ways:-
1) Add a record to create a new index
2) Alter a record to change an index
3) Delete a record in order to remove an index that is no longer neededThanks for the explanation. That's a slightly unusual way of working, but I'm sure you have your reasons.
Hi Phil
Yeah, it's supposed to help us keep track of our indexes and make sure our maintenance place is working.
I've just tried the solution you pointed me to, but it's given me no indexes whatsoever on our test server and I know for a fact there are around 2000 of them sprinkled across various databases. Any ideas?
ScottPletcher - Tuesday, April 17, 2018 9:10 AMI still don't understand why you need the index in string format to determine that. It's actually easier to accurately compare the meta-data rather than a string definition (where noncritical things like fillfactor and ON DELETE CASCADE would cause index diffs to be seen even when all the other things in the index are still identical).
Hi Scott
We want to store the index data (database name, table name, indexed columns, included columns) somewhere so we can track them.
We also want them in a convenient format so we can add/remove/alter as necessary.
I'm open to ideas if you have any suggestions.
April 17, 2018 at 10:15 am
richardmgreen1 - Tuesday, April 17, 2018 9:16 AMPhil Parkin - Tuesday, April 17, 2018 8:58 AMrichardmgreen1 - Tuesday, April 17, 2018 8:51 AMHi PhilApologies, I've been away from work and not had chance to work on your suggestion.
I was responding to Scott's post with the purpose of my question.
The idea is to preload the table once with the current indexes (hence my original question and your answer).
After that, the table will simply be used in one of three ways:-
1) Add a record to create a new index
2) Alter a record to change an index
3) Delete a record in order to remove an index that is no longer neededThanks for the explanation. That's a slightly unusual way of working, but I'm sure you have your reasons.
Hi Phil
Yeah, it's supposed to help us keep track of our indexes and make sure our maintenance place is working.
I've just tried the solution you pointed me to, but it's given me no indexes whatsoever on our test server and I know for a fact there are around 2000 of them sprinkled across various databases. Any ideas?ScottPletcher - Tuesday, April 17, 2018 9:10 AMI still don't understand why you need the index in string format to determine that. It's actually easier to accurately compare the meta-data rather than a string definition (where noncritical things like fillfactor and ON DELETE CASCADE would cause index diffs to be seen even when all the other things in the index are still identical).Hi Scott
We want to store the index data (database name, table name, indexed columns, included columns) somewhere so we can track them.
We also want them in a convenient format so we can add/remove/alter as necessary.
I'm open to ideas if you have any suggestions.
There's a reasonably well-known solution for such things... It's called source control software. TFS comes to mind in terms of possibly easiest to deploy changes or detect differences, as then all you need is a no-data containing metadata copy of your database and TFS, at least in theory, should be able to identify differences. Is it easy or inexpensive? ... hell no. But it does have the advantage of being known to work. What you're doing is reproducing part of its functionality... That could be good, or bad...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 18, 2018 at 6:22 am
Hi Steve
I know about TFS (although I'm fair from an expert).
I'm not clear on how we could track indexes and have them in a convenient place to keep them to see what we've already got that could do with tweaking.
As usual, I'm open to pointers and new (to me) knowledge.
April 18, 2018 at 12:19 pm
richardmgreen1 - Wednesday, April 18, 2018 6:22 AMHi SteveI know about TFS (although I'm fair from an expert).
I'm not clear on how we could track indexes and have them in a convenient place to keep them to see what we've already got that could do with tweaking.As usual, I'm open to pointers and new (to me) knowledge.
You would store the index creation scripts in TFS, and if you do your database changes exclusively through TFS, you can compare one database with another and see ALL the differences in structure.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 18, 2018 at 12:27 pm
richardmgreen1 - Wednesday, April 18, 2018 6:22 AMHi SteveI know about TFS (although I'm fair from an expert).
I'm not clear on how we could track indexes and have them in a convenient place to keep them to see what we've already got that could do with tweaking.As usual, I'm open to pointers and new (to me) knowledge.
I suggest that you read the Stairway to Database Source Control for additional background.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 19, 2018 at 4:09 am
We're looking to move to TFS for database version control in the very near future.
I just wanted a system that we could set up in the meantime.
Looks like we're bringing our TFS changes forward in our calendar.
April 21, 2018 at 6:28 am
In Powershell....exporting to Excel
$Path = "C:\temp\Get-Indexes.sql"
$tsql = Get-Content -Path $Path -Raw
foreach( $ServerInstance in $ServerInstances)
{
$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance ;
$SmoDatabase = New-Object Microsoft.SqlServer.Management.Smo.Database
$Indexes = @()
foreach($Database in $($SmoServer.Databases | Where-Object {!($_.IsSystemObject) -And $_.name -ne "tobeexcluded"}| Select -ExpandProperty name) )
{
$Batch = New-Object -TypeName:Collections.Specialized.StringCollection
$Batch.AddRange($tsql)
$SmoDatabase = $SmoServer.Databases.Item($Database)
$indexes += $SmoDatabase.ExecuteWithResults($Batch).Tables[0]
}
$WorkSheet = "Indexes_$($ServerInstance.Replace('\','_'))"
$IndexReport = "C:\temp\Indexes.xlsx"
$Indexes | Select -Property Database, TableName, IndexName,IndexColumns,IncludeColumns | Export-Excel $IndexReport -WorkSheetName $WorkSheet -FreezeTopRow -BoldTopRow
}
Iain
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply