A Second Set of Eyes

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!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.

The table Fragmentation is actually a permanent table, not a temp table.

..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"

Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).

Regards,

hmscott

PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by

exec ('use mydb select * from mytable')

A question about udf versus sp in a specific context...

Given the following objective:

1. Assume that I have a table that contains two fields: an auto-numbered id and an integer value
2. Check to see if a record exists in a table based on a parameter query of the integer value
3. If the record exists, return the record id
4. If the record does not exist, insert a new record into the table (using the parameter value as data) and return the auto-numbered id of the new record

I can do each of these things as a sequence of individual steps, of course, but it seems to me that I ought to be able to do it with a single udf (or perhaps a specialized query) that would be more efficient. I couldn't find something like this in the beginning SQL Express books I have on hand and I also didn't find anything exactly on point on this newsgroup or a search of Google. However, I am sure the answer is 'out there' and I am hoping that someone can point me in the right direction. Thanks!

Duncan

In thinking further about my original question and digging into one of my old SQL 2000 books, I concocted the following usp which seems to work:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT
AS
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF @.@.ROWCOUNT = 1
RETURN @.kit_id
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
RETURN @.kit_id
END

Does anyone see any particular issues with the above code? Thanks.

Duncan

|||

Perhaps something like this:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT * FROM kits WHERE product_variant_id = @.product_variant_id)

BEGIN
SELECT @.kit_id = id FROM kits WHERE product_variant_id = product_variant_id
END
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END

SET NOCOUNT OFF

END

|||Thanks for helping me out!|||

To give back in a small way, here is a slightly modified version of my earlier repsonse:

CREATE PROC [dbo].[usp_GetKitId] @.kit_id int OUTPUT, @.product_variant_id int
AS
SET NOCOUNT ON
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF NOT @.@.ROWCOUNT = 1
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END
SET NOCOUNT OFF

Then called from code-behind like so:

PublicSharedFunction GetKitId(ByVal product_variant_id)AsInteger'If a kit exists for the product variant, return the kit id; if a kit does'not exist for the product variant, create a new kit and return the new'kit's id.Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("atheniqueonlineConnectionString").ConnectionStringDim connectionAsNew SqlConnection(connectionString)Dim cmdAsNew SqlCommandDim kitIdAsIntegerWith cmd

.Connection = connection

.CommandText =

"usp_GetKitId"

.CommandType = CommandType.StoredProcedure

.Parameters.Clear()

.Parameters.Add(

New SqlParameter("kit_id", SqlDbType.Int))

.Parameters(

"kit_id").Direction = ParameterDirection.Output

.Parameters(

"kit_id").Value = 0

.Parameters.Add(

New SqlParameter("product_variant_id", SqlDbType.Int))

.Parameters(

"product_variant_id").Direction = ParameterDirection.Input

.Parameters(

"product_variant_id").Value = product_variant_idEndWithTry'Fill table

connection.Open()

Try

cmd.ExecuteNonQuery()

Catch exAs Exception'Handle exceptionsEndTryCatch eAs SqlException' Handle exceptionsFinally

connection.Close()

EndTry'Return the result

kitId = cmd.Parameters(

"kit_id").ValueReturn kitIdEndFunction