Looping across databases

Question

Post reply

Looping across databases

sql-lover

SSCoach

Points: 18530
More actions
June 7, 2016 at 12:31 pm

#327434

Dynamic SQL is not my forte. Can someone give me a hand and tell me how to loop this TSQL code across all user databases on a given instance?
--SET NOCOUNT ON;
DECLARE @indexName VARCHAR(128);
--Save column list on a variable
DECLARE @rclist nvarchar(max);
SET @rclist='LocationID, SaleType, EmployeeID, CheckID';
DECLARE @rilist nvarchar(max);
SET @rilist='Amount, DaypartID, RevenueCenterID, TransactionTime';
IF (EXISTS (SELECT
OBJECT_NAME(SP.object_id) AS TABLE_NAME
,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME
FROM sys.partitions SP
INNER JOIN sys.tables ST
ON SP.object_id = ST.object_id
WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)='MyTable' ))
BEGIN
SELECT
INDEX_DATA.name AS index_name
, STUFF(( SELECT ', ' + COLUMN_DATA_KEY_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS key_column_list
, STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS include_column_list
INTO #IndexDetails
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND TABLE_DATA.name='MyTable'
AND INDEX_DATA.type_desc='NONCLUSTERED'
AND INDEX_DATA.is_primary_key<>1
--Eliminating any bad Index via CURSOR.
DECLARE [indexes] CURSOR FOR
SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist
OPEN [indexes]
FETCH NEXT FROM [indexes] INTO @indexName
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('DROP INDEX [' + @indexName + '] ON MyTable')
FETCH NEXT FROM [indexes] INTO @indexName
END
CLOSE [indexes]
DEALLOCATE [indexes]
DROP TABLE #IndexDetails;
--This is the Index creation part.
SELECT
INDEX_DATA.name AS index_name
, STUFF(( SELECT ', ' + COLUMN_DATA_KEY_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS key_column_list
, STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR
XML PATH('')
), 1, 2, '') AS include_column_list
INTO #NewIndex
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND TABLE_DATA.name='MyTable'
AND INDEX_DATA.type_desc='NONCLUSTERED'
IF NOT EXISTS
(
SELECT *
FROM #NewIndex
WHERE key_column_list=@rclist AND include_column_list=@rilist
)
BEGIN
CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]
(
[LocationID] ASC,
[SaleType] ASC,
[EmployeeID] ASC,
[CheckID] ASC
)
INCLUDE ( [Amount],
[DaypartID],
[RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
END
DROP TABLE #NewIndex;
END
What it does is clean up existing no clustered indexes and deploy a new one based on Index definition. If the new Index already exists, it won't create it, of course.
The script actually works. I manually ran it via SSMS and works without any issues.
Any suggestion?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76997 More actions · Answer 1

Lookup sys.sp_MSforeachdb

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

You should find that this[/url], by Aaron Bertrand, allows you to do it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sql-lover SSCoach Points: 18530 More actions · Answer 3

drew.allen (6/7/2016)
Lookup sys.sp_MSforeachdb
Drew

Can you post actual TSQL with it??? Got some syntax errors when trying that.

sql-lover SSCoach Points: 18530 More actions · Answer 4

Phil Parkin (6/7/2016)
You should find that this[/url], by Aaron Bertrand, allows you to do it.

This is cool! Let me take a look.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

Some pratting about is required to handle the single quotes embedded in your SQL, before it can be passed to an executor proc.

One way is to replace all single quotes with another character and then do another replace after assignment. I used a tilde in the following code:

DECLARE @command VARCHAR(MAX);

SET @command = 'DECLARE @indexName VARCHAR(128);

--Save column list on a variable

DECLARE @rclist nvarchar(max);

SET @rclist=~LocationID, SaleType, EmployeeID, CheckID~;

DECLARE @rilist nvarchar(max);

SET @rilist=~Amount, DaypartID, RevenueCenterID, TransactionTime~;

IF (EXISTS (SELECT

OBJECT_NAME(SP.object_id) AS TABLE_NAME

,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME

FROM sys.partitions SP

INNER JOIN sys.tables ST

ON SP.object_id = ST.object_id

WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)=~MyTable~ ))

BEGIN

SELECT

INDEX_DATA.name AS index_name

, STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

FROM sys.tables AS T

INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

FOR

XML PATH(~~)

), 1, 2, ~~) AS key_column_list

, STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

FROM sys.tables AS T

INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

FOR

XML PATH(~~)

), 1, 2, ~~) AS include_column_list

INTO #IndexDetails

FROM sys.indexes INDEX_DATA

INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

WHERE TABLE_DATA.is_ms_shipped = 0

AND TABLE_DATA.name=~MyTable~

AND INDEX_DATA.type_desc=~NONCLUSTERED~

AND INDEX_DATA.is_primary_key<>1

--Eliminating any bad Index via CURSOR.

DECLARE [indexes] CURSOR FOR

SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE (~DROP INDEX [~ + @indexName + ~] ON MyTable~)

FETCH NEXT FROM [indexes] INTO @indexName

END

CLOSE [indexes]

DEALLOCATE [indexes]

DROP TABLE #IndexDetails;

--This is the Index creation part.

SELECT

INDEX_DATA.name AS index_name

, STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

FROM sys.tables AS T

INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

FOR

XML PATH(~~)

), 1, 2, ~~) AS key_column_list

, STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

FROM sys.tables AS T

INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

FOR

XML PATH(~~)

), 1, 2, ~~) AS include_column_list

INTO #NewIndex

FROM sys.indexes INDEX_DATA

INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

WHERE TABLE_DATA.is_ms_shipped = 0

AND TABLE_DATA.name=~MyTable~

AND INDEX_DATA.type_desc=~NONCLUSTERED~

IF NOT EXISTS

(

SELECT *

FROM #NewIndex

WHERE key_column_list=@rclist AND include_column_list=@rilist

)

BEGIN

CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]

(

[LocationID] ASC,

[SaleType] ASC,

[EmployeeID] ASC,

[CheckID] ASC

)

INCLUDE ( [Amount],

[DaypartID],

[RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

END

DROP TABLE #NewIndex;

END';

SET @command = REPLACE(@command, '~', '''');

SELECT @command;

@Command is now in the correct form ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sql-lover SSCoach Points: 18530 More actions · Answer 6

Phil Parkin (6/7/2016)
Some pratting about is required to handle the single quotes embedded in your SQL, before it can be passed to an executor proc.
One way is to replace all single quotes with another character and then do another replace after assignment. I used a tilde in the following code:
DECLARE @command VARCHAR(MAX);
SET @command = 'DECLARE @indexName VARCHAR(128);
--Save column list on a variable
DECLARE @rclist nvarchar(max);
SET @rclist=~LocationID, SaleType, EmployeeID, CheckID~;
DECLARE @rilist nvarchar(max);
SET @rilist=~Amount, DaypartID, RevenueCenterID, TransactionTime~;
IF (EXISTS (SELECT
OBJECT_NAME(SP.object_id) AS TABLE_NAME
,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME
FROM sys.partitions SP
INNER JOIN sys.tables ST
ON SP.object_id = ST.object_id
WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)=~MyTable~ ))
BEGIN
SELECT
INDEX_DATA.name AS index_name
, STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR
XML PATH(~~)
), 1, 2, ~~) AS key_column_list
, STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR
XML PATH(~~)
), 1, 2, ~~) AS include_column_list
INTO #IndexDetails
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND TABLE_DATA.name=~MyTable~
AND INDEX_DATA.type_desc=~NONCLUSTERED~
AND INDEX_DATA.is_primary_key<>1
--Eliminating any bad Index via CURSOR.
DECLARE [indexes] CURSOR FOR
SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist
OPEN [indexes]
FETCH NEXT FROM [indexes] INTO @indexName
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (~DROP INDEX [~ + @indexName + ~] ON MyTable~)
FETCH NEXT FROM [indexes] INTO @indexName
END
CLOSE [indexes]
DEALLOCATE [indexes]
DROP TABLE #IndexDetails;
--This is the Index creation part.
SELECT
INDEX_DATA.name AS index_name
, STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR
XML PATH(~~)
), 1, 2, ~~) AS key_column_list
, STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR
XML PATH(~~)
), 1, 2, ~~) AS include_column_list
INTO #NewIndex
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND TABLE_DATA.name=~MyTable~
AND INDEX_DATA.type_desc=~NONCLUSTERED~
IF NOT EXISTS
(
SELECT *
FROM #NewIndex
WHERE key_column_list=@rclist AND include_column_list=@rilist
)
BEGIN
CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]
(
[LocationID] ASC,
[SaleType] ASC,
[EmployeeID] ASC,
[CheckID] ASC
)
INCLUDE ( [Amount],
[DaypartID],
[RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
END
DROP TABLE #NewIndex;
END';
SET @command = REPLACE(@command, '~', '''');
SELECT @command;
@Command is now in the correct form ...

That's cool, Phil. So I guess that's what I need in order to use sp_MSforeachdb?

Phil Parkin SSC Guru Points: 246987 More actions · Answer 7

Yes, but ...

I'd suggest trying out a couple of non-destructive queries first ... and definitely in a test environment ... just to get familiar with it.

Note the comments about sp_msForeachDB sometimes missing databases & keep an eye on that. The Aaron Bertrand link I posted avoids this problem.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel