Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 problem using sp_msforeachdb when "-" in db name
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cubehead
Starting Member

4 Posts

Posted - 12/02/2012 :  10:46:14  Show Profile  Reply with Quote
Hey all,

I'm using the script below to re-org or rebuild indexes in all my SQL 2005 production databases. Everything works great until I tried to run it on a server which has a db that has a dash "-" in its name, then it craps out with a "Could not locate entry in sysdatabases for database 'partial_DB_name_before_the_dash'. No entry found with that name. Make sure that the name is entered correctly." I have been trying different things but none works so far. Any ideas on how to fix this?

Thanks very much.

DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL

DECLARE @Defrag NVARCHAR(MAX)

CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL)

EXEC sp_msforeachdb 'Use ?;
INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
)
Select db_name()
,object_name(s.object_id) As ObjectName
,object_name(s.object_id) As SchemaName
,i.Name As IndexName
,s.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s
Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id
Where i.index_id > 0
And i.index_id < 255
And s.avg_fragmentation_in_percent > 20'

DECLARE cList CURSOR
FOR SELECT * FROM #Frag

OPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE'
EXEC sp_executesql @Defrag
END
ELSE IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REBUILD'
EXEC sp_executesql @Defrag
END

FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag

END
CLOSE cList
DEALLOCATE cList

DROP TABLE #Frag

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  10:50:11  Show Profile  Reply with Quote
quote:
Originally posted by cubehead

Hey all,

I'm using the script below to re-org or rebuild indexes in all my SQL 2005 production databases. Everything works great until I tried to run it on a server which has a db that has a dash "-" in its name, then it craps out with a "Could not locate entry in sysdatabases for database 'partial_DB_name_before_the_dash'. No entry found with that name. Make sure that the name is entered correctly." I have been trying different things but none works so far. Any ideas on how to fix this?

Thanks very much.

DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL

DECLARE @Defrag NVARCHAR(MAX)

CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL)

EXEC sp_msforeachdb 'Use ?;
INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
)
Select db_name()
,object_name(s.object_id) As ObjectName
,object_name(s.object_id) As SchemaName
,i.Name As IndexName
,s.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s
Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id
Where i.index_id > 0
And i.index_id < 255
And s.avg_fragmentation_in_percent > 20'

DECLARE cList CURSOR
FOR SELECT * FROM #Frag

OPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + '[' @DBName + ']' + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE'
EXEC sp_executesql @Defrag
END
ELSE IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' +
'[' + @DBName + ']'
+ '.' + @SchemaName + '.' + @TableName + ' REBUILD'
EXEC sp_executesql @Defrag
END

FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag

END
CLOSE cList
DEALLOCATE cList

DROP TABLE #Frag





Try with the red one. Also check Tara's script for it

Edited by - sodeep on 12/02/2012 10:51:03
Go to Top of Page

cubehead
Starting Member

4 Posts

Posted - 12/02/2012 :  11:00:52  Show Profile  Reply with Quote
sodeep,

Thank you for the quick response. It didn't work with that fix. I think the problem occurs before it got to that point. I suspect it's happening at the "use ?" part. Could you post a link to Tara's script?

Thanks very much.

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  11:13:01  Show Profile  Reply with Quote
Forgot to mention. Put bracket around ? like []

Edited by - sodeep on 12/02/2012 11:15:01
Go to Top of Page

cubehead
Starting Member

4 Posts

Posted - 12/02/2012 :  17:44:34  Show Profile  Reply with Quote
sodeep,

Thanks for the help. That took care of it.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  19:38:23  Show Profile  Reply with Quote
Welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000