A Second Set Of Eyes
Apr 10, 2007
This is more of a "does anyone see something I'm missing" post versus a real problem.
What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.
I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @DBNAME VARCHAR(64)
DECLARE @tablename varchar(128)
DECLARE @execstr varchar(255)
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @maxextfrag decimal
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0
SELECT @maxextfrag = 40.0
DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')
---- Declare a 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 db
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'USE ' + @dbname + ';' + char(13)
PRINT @execstr
EXEC (@execstr)
-- 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 TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables
SELECT @SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @SQLSTRING = @SQLSTRING + @DBNAME
SELECT @SQLSTRING = @SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag'
PRINT @SQLSTRING
EXEC(@SQLSTRING)
FETCH NEXT
FROM db
INTO @DBNAME
END
CLOSE db
DEALLOCATE db
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag
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
Again, thanks!!
View 4 Replies
Feb 9, 2006
my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.
the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/Newindex/trip_types.asp, line 53
line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.
any comments gratefully received thanks.
View 6 Replies
View Related
Sep 13, 2007
when I try to create this SP I get: "incorrect syntax near @MyResult"
I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16) ,
@MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
begin
@MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
declare @totalFails int
Select @totalFails = Count(*) From FailedLogons
Where UserName = @UserName
And dtFailed > GetDate()-1
if (@totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @UserName
@MyResult = 0
end
View 3 Replies
View Related
Apr 20, 2006
greets, im coding a few queries to a table. im storing sets of records into the table, each set of records will haev a different batch id. so basically 2 sets of records can occupy this table at the same time, and their batch id is the main key (with 2 other fields also being PKs). i want to compare the 2 sets in the same table and get the differences:
1. records that were added
2. records that were updated
3. records that were deleted
ive written queries for the added records, and the updated records but i cant get the query for finding deleted records. the logic looks good to me but im obviously missing something so i could use a fresh pair of eyes.
here is the table def:
Code:
CREATE TABLE [dbo].[UPCXREF_BATCH] (
[BATCH_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHAIN_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_CODE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITM_CATEGORY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UPCXREF_BATCH] WITH NOCHECK ADD
CONSTRAINT [PK_UPCXREF_BATCH] PRIMARY KEY CLUSTERED
(
[BATCH_ID],
[CHAIN_CODE],
[UPC]
) ON [PRIMARY]
GO
and here are the queries ive gotten so far
Code:
/* This section retrieves all UPDATED records */
SELECT 'U' as FLAG, b2.CHAIN_CODE as CHAIN_CODE, b2.UPC, b2.ITM_CODE, b2.ITM_CATEGORY
FROM UPCXREF_BATCH b2 INNER JOIN UPCXREF_BATCH b1
ON b2.CHAIN_CODE=b1.CHAIN_CODE
AND b2.UPC=b1.UPC
AND (b2.ITM_CODE<>b1.ITM_CODE OR b2.ITM_CATEGORY <> b1.ITM_CATEGORY )
WHERE b2.BATCH_ID='BTC0002' AND b1.BATCH_ID='BTC0001'
/* This section retrieves all NEW records */
SELECT 'A' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0002'
AND (CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001')
OR UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001'))
here was my attempt to get deleted records which looks like it makes sense but isnt working
Code:
SELECT 'D' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0001'
AND CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
AND UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
here batch 'BTC0001' is the older set of records that already existed and batch 'BTC0002' is the new set we just inserted into the table. am i missing something else?
View 11 Replies
View Related
Jun 4, 2007
I have three tables:
Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…
I need help with a query to display the following:
Category name, # of topics, # of posts
Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562
It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7
Overview: use category id to get count of topics then use the topic id to get the count of posts.
SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName
Thanks in advance
View 6 Replies
View Related