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

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 creating sp in master and using for all databases

Author  Topic 

schinni
Yak Posting Veteran

66 Posts
Posted - 2003-05-28 : 19:03:21
Hello,

This is from sqlbol,it's a good one and I wanted to make sp and
create it in master database and use it for all databases
---
create sp_test @dbname sysname
as

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist


and execute as
sp_test 'Northwind'


Thanks,






















Edited by - schinni on 05/29/2003 08:35:30

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-05-28 : 19:06:52
Is there a question?

Tara
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2003-05-28 : 19:23:55


Go to Top of Page

schinni
Yak Posting Veteran

66 Posts
Posted - 2003-05-28 : 20:28:24
the question is i couldn't create in the master database
i can create but it doesn't work for other databases on the machine.i
want to know how to modify the sp so that it works for all databases
on the machine
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-05-28 : 20:35:55
What is the error that you got when you tried to create it in the master database? Does SQL Server Books Online give you any details about it? If you want us to help with this, you'll need to let us know how to find the stored procedure in BOL so that we can read the related information. We also need to know all of the errors that you are getting?

Looking at the stored procedure, it appears that it isn't written correctly. It accepts @dbname as an input parameter but doesn't ever use @dbname for anything. All table names should be prefaced with @dbname probably otherwise you'll have to run this stored procedure in the user database.

Try creating the stored procedure in Northwind (remove @dbname sysname from stored procedure though cuz it's not used anyway). Then run: EXEC sp_test...Does it work?

Tara
Go to Top of Page

schinni
Yak Posting Veteran

66 Posts
Posted - 2003-05-28 : 20:42:23
yes it works if i create sp_test in northwind database
it is in sql2k bol for dbcc showcontig

Thanks,
Go to Top of Page

schinni
Yak Posting Veteran

66 Posts
Posted - 2003-05-28 : 22:14:42
quote:

Hello,

This is from sqlbol,it's a good one and I wanted to make sp and
create it in master database and use it for all databases
---
create sp_test @dbname sysname
as

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist


and execute as
sp_test 'Northwind'


Thanks,
Go to Top of Page

schinni
Yak Posting Veteran

66 Posts
Posted - 2003-05-29 : 19:08:56
hope the question is clear

Thanks,
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-05-29 : 20:01:55
In order to be able to do this, DBCC SHOWCONTIG would have to allow the 3 part name for the table, such as DB1.dbo.Table1. The problem is that DBCC SHOWCONTIG only allows the 2 part name, such as [dbo.Table1] and yes the brackets are required, or the 1 part name, such as Table1. Since you can not do a USE statement in the stored procedure, you can not switch databases so that it runs in each of the databases. So I believe that the answer is that you will have to create this stored procedure in each of your user databases and run it in each of your user databases. Maybe someone here can prove me wrong, but I just don't think that this is possible in a stored procedure.

Tara
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 12:26:49
Hi,

I decided to implement this too and ran into the same issue. I've modified the BOL SQL posted below so that it should address each table by owner name. I tried to highlight changes I made for easier reading.

Anyone see any problem with it? Comments, suggestions, recommendations appreciated.


Use MyDB
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @ownername VARCHAR (128)
DECLARE @fullname VARCHAR (255)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME, TABLE_SCHEMA
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

CREATE TABLE #fraglink (
	ObjectName CHAR (255),
	ObjectOwner CHAR (255))

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename, @ownername

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   SET @fullname = @ownername + '.' + @tablename
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @fullname + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   INSERT #fraglink (ObjectName,ObjectOwner) VALUES (@tablename,@ownername) 
  
FETCH NEXT
      FROM tables
      INTO @tablename, @ownername
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT #fraglink.ObjectOwner AS ObjectOwner, #fraglist.ObjectName AS ObjectName,
	 #fraglist.ObjectId AS ObjectId, #fraglist.IndexId AS IndexId, 
         #fraglist.LogicalFrag AS LogicalFrag
   FROM #fraglink INNER JOIN #fraglist ON #fraglink.ObjectName = #fraglist.ObjectName
   WHERE #fraglist.LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (#fraglist.ObjectId, #fraglist.IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @ownername, @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @fullname = RTRIM(@ownername) + '.' + RTRIM(@tablename)
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + @fullname + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @ownername, @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
DROP TABLE #fraglink
GO
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 13:22:48
BTW, this works in testing, I'm looking for comments on efficiency, performance, etc.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-04-18 : 13:54:23
You can run DBCC SHOWCONTIG against another database, but you have to do it inside an EXECUTE statement and have a USE database command before the DBCC SHOWCONTIG.


use tempdb

exec ('
--Switch to pubs database
use pubs
DBCC SHOWCONTIG ( authors )
')


CODO ERGO SUM
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 17:16:16
Actually, I intend to call this as stored procedure. Something like:

sp_defragdb 'MyDB'

That way I can call it upon conditional outcomes of other maintenance items.

1) Full backup of DB --> Continue on success, quit and report failure
2) Remove files committed to tape --> Continue on success/failure
3) Check DB health --> Continue on success, quit and report failure
4) Defrag Indexes --> Quit on success, quit and report failure
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site