April 10, 2017 at 11:06 am
Hi All,
In short: trying to check the existence of indexes across all DBs, and input into a table if not there. I've tried a few different ways of doing this with no luck. The query needs to 1) Set table and Index names 2) Set DB 3) Execute query 4)Set Next Table and Index names/Execute until no more in DB 5) Move onto next DB and repeat.
VW_DATABASES is just a table that stores the names of all of our DBs. IDX check is the same but for the indexes/tables that should be in the DBs.
IF OBJECT_ID('tempdb..#nonexistent') IS NOT NULL
DROP TABLE #nonexistent
CREATE TABLE #nonexistent (
IndexName VARCHAR(300) NULL,
TableName VARCHAR(300) NULL,
DB VARCHAR(300) NULL
)
DECLARE @Table VARCHAR(300)
DECLARE @index VARCHAR(300)
DECLARE @SQL NVARCHAR(MAX)
SELECT
@Table = TableName,
@index = IndexName
FROM MONITORING..IDXCHECK
DECLARE INDEXCHECK CURSOR FOR
SELECT 'IF NOT EXISTS(SELECT * FROM ['+name+'].sys.indexes WHERE name = '''+@Index+''' AND object_id = OBJECT_ID('''+@Table+'''))
BEGIN
INSERT INTO #nonexistent (IndexName,TableName,DB)
VALUES ('''+@Index+''','''+@Table+''','''+name+''')
END'
FROM MONITORING..VW_DATABASES
OPEN INDEXCHECK
FETCH NEXT FROM INDEXCHECK INTO @SQL
WHILE (@@FETCH_STATUS =0)
BEGIN
EXECUTE sp_executesql @sql
FETCH NEXT FROM INDEXCHECK INTO @SQL
END
CLOSE INDEXCHECK
DEALLOCATE INDEXCHECK
SELECT
*
FROM #nonexistent
I've tried nesting cursors for the table/index names, but that would typically return nothing. I'm not opposed to getting away from using cursors entirely for this, just don't know how to go across all DBs without it.
Thank you for your time, yet again!
April 10, 2017 at 11:14 am
I don't think you need a cursor for the index checks, although it's probably easiest to use a cursor to go thru the databases. See if the code below will work for you within each db:
INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,DB_NAME() AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2017 at 4:26 pm
As an alternate to using a cursor for each DB, could you use:EXEC sp_Msforeachdb 'INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,DB_NAME() AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL'
NOTE - I did not try running that, just thinking it removes the cursor for you. Still need a lot of the other code you had, but this way you don't need a cursor for the databases (unless you are not running this against all databases).
The biggest assumption here is that you want to run the code against all databases which is my understanding. If the view returns only a subset of all of the databases then the above will not be helpful.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 10, 2017 at 5:30 pm
bmg002 - Monday, April 10, 2017 4:26 PMAs an alternate to using a cursor for each DB, could you use:EXEC sp_Msforeachdb 'INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,DB_NAME() AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL'NOTE - I did not try running that, just thinking it removes the cursor for you. Still need a lot of the other code you had, but this way you don't need a cursor for the databases (unless you are not running this against all databases).
The biggest assumption here is that you want to run the code against all databases which is my understanding. If the view returns only a subset of all of the databases then the above will not be helpful.
Just to be a stickler on semantics, the use of sp_Msforeachdb isn't an alternative to "using" a cursor because it is cursor based. It just keeps you from having to "write" one. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 8:24 am
Jeff Moden - Monday, April 10, 2017 5:30 PMbmg002 - Monday, April 10, 2017 4:26 PMAs an alternate to using a cursor for each DB, could you use:EXEC sp_Msforeachdb 'INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,DB_NAME() AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL'NOTE - I did not try running that, just thinking it removes the cursor for you. Still need a lot of the other code you had, but this way you don't need a cursor for the databases (unless you are not running this against all databases).
The biggest assumption here is that you want to run the code against all databases which is my understanding. If the view returns only a subset of all of the databases then the above will not be helpful.Just to be a stickler on semantics, the use of sp_Msforeachdb isn't an alternative to "using" a cursor because it is cursor based. It just keeps you from having to "write" one. 😀
That is a good point. I know I like using that and sp_msforeachtable over writing my own cursor for it. One of those "why reinvent the wheel?" moments. That being said, if nobody reinvented the wheel, we'd still have stone wheels...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 11, 2017 at 8:34 am
So, maybe I'm missing something, here. I can run the provided solution (thank you very much, btw! also, I feel like an idiot because that's a really simple solution. brain fart) in the context of one DB and it will produce a row. I go to check the integrity of the results, and the index is not there. That means it's working.
However, if I set up the query to run across all DBs with a cursor (including adding [DBName] to sys.indexes), it goes all screwy and produces a bunch of unexpected results. Most of the results in this case are actually not correct. My understanding was that if one were to add the DB to the table name, the context selected for the window in SSMS doesn't matter. Apparently, I'm wrong. Below is what the cursor looks like.
IF OBJECT_ID('tempdb..#nonexistent') IS NOT NULL
DROP TABLE #nonexistent
CREATE TABLE #nonexistent (
IndexName VARCHAR(300) NULL,
TableName VARCHAR(300) NULL,
DB VARCHAR(100) NULL
)
DECLARE @SQL NVARCHAR(MAX)
DECLARE IDX CURSOR FOR
SELECT
'
INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,'''+name+''' AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN ['+name+'].sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL
'
FROM MONITORING..VW_DATABASES
OPEN IDX
FETCH NEXT FROM IDX INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @SQL
FETCH NEXT FROM IDX INTO @SQL
END
CLOSE IDX
DEALLOCATE IDX
SELECT
*
FROM #nonexistent
ORDER BY DB,TableName
and below is a resulting query from the cursor.
SELECT ic.IndexName,ic.TableName,'DBName' AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN [DBName].sys.indexes i
ON i.name = ic.IndexName
AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL
Am I wrong in my understanding of prefixing table names with DBs, or is there likely something else going on, here?
April 11, 2017 at 9:23 am
Yeah, OBJECT_ID defaults to the current db only. Easiest is to adjust the code to shift context to each db, something like this:
DECLARE IDX CURSOR FOR
SELECT
'USE [' + name + '];
INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,'''+name+''' AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL
'
FROM MONITORING..VW_DATABASES
ORDER BY name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 11, 2017 at 9:42 am
ScottPletcher - Tuesday, April 11, 2017 9:22 AMYeah, OBJECT_ID defaults to the current db only. Easiest is to adjust the code to shift context to each db, something like this:
DECLARE IDX CURSOR FOR
SELECT
'USE [' + name + '];
INSERT INTO #nonexistent (IndexName,TableName,DB)
SELECT ic.IndexName,ic.TableName,'''+name+''' AS DB
FROM MONITORING.dbo.IDXCHECK ic
LEFT OUTER JOIN sys.indexes i ON i.name = ic.IndexName AND i.object_id = OBJECT_ID(ic.TableName)
WHERE i.name IS NULL
'
FROM MONITORING..VW_DATABASES
ORDER BY name
Ah! That explains it. And actually, that's exactly what I did to get the right results; just didn't understand the WHY. Thank you again! Huge help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply