November 23, 2005 at 5:28 am
Below is the correct code, looks like a typo in the article, somewhat embarassing, but no more than MS crashing SQL 2k5 and having to reboot in the NY launch event yesterday.
-----------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @dbname VARCHAR(20)
DECLARE @sql VARCHAR(1000)
DECLARE @inserttable VARCHAR(3200)
-- Create the table
CREATE TABLE #DBFRAGMENT (
ObjectName VARCHAR (50),
ObjectId INT,
IndexName VARCHAR (100),
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 #tablename (table_name varchar(400))
--DECLARE DB Cursor
DECLARE databases CURSOR FOR
SELECT NAME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME = @NAME
--Open the cursor
OPEN databases
FETCH NEXT FROM databases INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''
print @sql
insert into #tablename exec(@sql)
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM #tablename
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TABLENAME
-- Do the showcontig of all indexes of the table
INSERT INTO #DBFRAGMENT
EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tablename
END
set @inserttable ='INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
select ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname)) '''''
--PRINT @INSERTTABLE
EXEC (@inserttable)
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
delete from #tablename
delete from #DBFRAGMENT
FETCH NEXT FROM databases INTO @dbname
END
CLOSE databases
DEALLOCATE databases
drop table #tablename
--Delete the temporary table
DROP TABLE #DBFRAGMENT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
May 9, 2008 at 4:55 am
Hello Nicholas,
I know this subject was discussed some time ago, but I am new to SQL and was wondering if you had enhanced the original script yet. I am in the process of trying to understand how it works and would value any new information about running on multiple databases. I was also wondering if it is possible to email using SMTP, when you do not have SQL configured to use a mapi applicaton (outlook e.t.c.)
I am very new to becoming a SQL DB Admin. I have been around SQL for a while, but never got into the scripting side
Any help appreciated
May 21, 2008 at 1:08 pm
It is possible to email using SMTP.
Try xpsmtp.
We are using it successfully.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply