January 13, 2011 at 1:59 am
Hi there,
I've created a stored proc in master that I can call in any user database to rebuild indexes.
When I step into the user database and run the proc only the user tables in master show up.
How should I call the proc in a user database?
use ReportServer;
declare @dbsysname
set @db = db_name();
EXEC master.[dbo].[reindex_all_PJL] @db;
Thanks for all you help.
January 13, 2011 at 2:07 am
pjl0808 (1/13/2011)
Hi there,I've created a stored proc in master that I can call in any user database to rebuild indexes.
When I step into the user database and run the proc only the user tables in master show up.
How should I call the proc in a user database?
use ReportServer;
declare @dbsysname
set @db = db_name();
EXEC master.[dbo].[reindex_all_PJL] @db;
Thanks for all you help.
can you post me the sproc's script
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 13, 2011 at 2:38 am
Here you go. A simple cursor than pulls in the owner/schema as well as the table.
Thanks
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Alter proc [dbo].[reindex_all_PJL]
@DB sysname, @fillfactor INT = 90
as
set arithabort on;
declare @TableName VARCHAR(255),
@sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + name AS TableName
FROM sys.tables order by name
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @DB + '.'+ @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
print @sql
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
January 13, 2011 at 7:39 am
Your ALTER INDEX statement references a specific databases but your SELECT does not.
Instead of
SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + name AS TableName
FROM sys.tables
order by name
Try something like
SELECT OBJECT_SCHEMA_NAME([object_id], 4) + '.' + name AS TableName
FROM msdb.sys.tables
order by name
_____________________________________________________________________
- Nate
January 14, 2011 at 8:15 am
I added the db in the alter index to see if that worked. The select keeps referring back to master where the SP is located even though I have stepped into a user db.
Thanks anyway.
January 14, 2011 at 12:25 pm
pjl0808 (1/14/2011)
I added the db in the alter index to see if that worked.
Right you need to alter the select statement not the index statement...
Stored Proc:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[reindex_all_PJL]
@DB sysname, @fillfactor INT = 90
as
set arithabort on;
declare @TableName VARCHAR(255)
, @dbid SMALLINT
, @sql NVARCHAR(MAX)
, @paramdef NVARCHAR(255)
CREATE TABLE #tbls (name SYSNAME)
SELECT @dbid = database_id FROM sys.databases WHERE name = @DB
SET @sql = 'SELECT OBJECT_SCHEMA_NAME([object_id], ' + CAST(@dbid AS VARCHAR(5)) + ') + ''.'' + name AS TableName'
SET @sql = @sql + ' FROM ' + @DB + '.sys.tables'
SET @sql = @sql + ' order by name'
SET @paramdef = '@dbid SMALLINT, @dbname SYSNAME'
SELECT @sql
INSERT INTO #tbls
EXEC sp_executesql @sql, @paramdef, @dbid = @dbid, @dbname = @DB
DECLARE TableCursor CURSOR FOR
--SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + name AS TableName
--FROM sys.tables order by name
SELECT name AS TableName FROM #tbls
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @DB + '.'+ @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
print @sql
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Call to reindex MSDB:
EXEC [reindex_all_PJL] @DB = 'msdb'
Result:
ALTER INDEX ALL ON msdb.dbo.backupfile REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON msdb.dbo.backupfilegroup REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON msdb.dbo.backupmediafamily REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON msdb.dbo.backupmediaset REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON msdb.dbo.backupset REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON msdb.dbo.DTA_input REBUILD WITH (FILLFACTOR = 90)
...
_____________________________________________________________________
- Nate
January 14, 2011 at 2:07 pm
Thanks Man. I'll give that a shot on Monday. Looks good.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply