December 8, 2004 at 1:53 pm
I have a feeling there's an easy solution to this one, but here goes anyway. I have a stored proc which has as its only variable the name of the database that it will be working on. I can't seem to get it to change to the correct database by issuing
"use @dbName" (@dbname is a varchar holding the name of the database)
Is this possible?
December 8, 2004 at 2:11 pm
let's say this is your sp :
create proc MySP @DbName as varchar(100)
as
use @DbName
Select * from dbo.SysObjects
GO
You'd have to do something like this :
create proc MySP @DbName as varchar(100)
as
exec ('Select * from ' + @DbName + '.dbo.SysObjects')
GO
however there's a better way of doing this. You can create the same sp and put it in the master database like so (note the SP_ prefixe on the sp name) :
create proc SP_MySP
as
Select * from dbo.SysObjects
GO
what happens is that sql server tries to find all the sps that start with SP_ in the master database. However the statement is executed in the context of the database where the proc is called from. So if you create that sp and run this, you will see what I mean (85 and 134 rows affected) :
use master
GO
create proc SP_MySP
AS
Select * from dbo.SysObjects
GO
use PUBS
exec SP_MySP
use NorthWind
exec SP_MySP
drop proc SP_MySP
December 8, 2004 at 2:19 pm
It seems that my problem still remains though, at the bottom of your example you are using the pubs and then the northwind databases, which is where my problem lies, I will only know from the SQL script that is going to call the SP which databases I need to access, and it will change with each run.
December 8, 2004 at 2:31 pm
then you can try my first solution which would work.. but be aware that dynamic sql is pretty much considered an evil amongts dbas and advanced coders. Make sure nobody can toy with your script to hack the server. Make sure you validate that the parameter really is a database and you should be fine :
if exists (Select * from master.dbo.sysdatabases where name = @DbName)
begin
--do your stuff
end
else
begin
--raiseerror.. you got a bug or a hacker
end
December 8, 2004 at 2:37 pm
dynamic SQL is pretty much out here. So there's no way to programmatically change which database is in use? My goal here was to have a script that checked each database and examined the index fragmentation and would reindex anything that was too fragmented. All of the defragmenting code works fine, its just that it will only run on the currently selected database. I am also working on a similar script that would watch the log file size vs. amount in use and shrink it, but I think I'm going to run into the same problem there.
December 8, 2004 at 2:51 pm
then you may have a look at this
exec SP_MSFOREACHDB 'print ''?'' --Script that does your stuff here'
December 8, 2004 at 2:53 pm
On another note I've seen scripts that do defragmentation on this board. Maybe you could use that too.
December 9, 2004 at 8:43 am
Here is how I resolved the same problem.
Please do not kick me too hard
CREATE proc spIndexDefrag as
-- ////////////////////////////////////////////////////////////////////////////////////////
--
-- -- 2004-11-22
-- Defragment Indexes for all user-defined tables in the databases where
-- Index ScanDensity for an Index is less than a defined threshold.
--
--
--
-- ----------------------------------------------------------------------------------------
-- Objects:
-- Table [IndexDefraglog] - the defragmented index info.
--
-- ////////////////////////////////////////////////////////////////////////////////////////
SET NOCOUNT ON
-- =====================================================
-- Declare variables:
-- =====================================================
DECLARE @x BIT
DECLARE @i INT
DECLARE @ii INT
DECLARE @DbId INT
DECLARE @DatabaseName VARCHAR(256)
DECLARE @ObjectId INT
DECLARE @TableName VARCHAR(256)
DECLARE @IndexId INT
DECLARE @IndexName VARCHAR(256)
DECLARE @s-2 VARCHAR(8000)
DECLARE @MinScanDensity DECIMAL(9,2)
DECLARE @RecCount INTEGER
-- ======================================================
-- Setup these variable before starting:
-- ======================================================
SET @x = 0 -- Debug Print
SET @MinScanDensity = 50.0 -- Defrag Threshold
SET @DatabaseName = ''
SET @TableName = ''
SET @IndexName = ''
SET @i= 1
SET @ii= 1
SET @s = ''
SET @RecCount = 0
-- ----------------------
-- Clean the temp tables:
-- ----------------------
IF ((Object_ID('TempDB.dbo.##TableList')) IS NOT NULL) -- List of user-defined tables
DROP TABLE ##TableList
IF ((Object_ID('TempDB.dbo.##Dbcc')) IS NOT NULL) -- Result of DBCC SHOWCONTIG for a given table
DROP TABLE ##Dbcc
IF ((Object_ID('TempDB.dbo.##IndexList')) IS NOT NULL) --
DROP TABLE ##IndexList
IF ((Object_ID('TempDB.dbo.##IndexDepth')) IS NOT NULL)
DROP TABLE ##IndexDepth
-- ====================================================
-- Build a list of tables for all DB to be maintained:
--
-- Add a DATABASE to the Union Select Statement
-- ====================================================
SELECT Identity(INT, 1, 1) as RowId, DATABASE_NAME, TABLE_NAME
INTO ##TableList
FROM
(
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM BakeryUSA.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM EliteSwine.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM Landmark.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM Larsen.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM MapleLeafMeats_Details.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM MapleLeafMeatsUSA.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT RTRIM(TABLE_CATALOG) as DATABASE_NAME, TABLE_NAME
FROM MapleLeafPoultry.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
) as T
ORDER BY DATABASE_NAME, TABLE_NAME
WHILE @i <= (Select Max(RowId) From ##TableList)
BEGIN
SELECT @DatabaseName = RTRIM(DATABASE_NAME), @TableName = '['+ RTRIM(TABLE_NAME) + ']'
FROM ##TableList
WHERE RowId = @i
if(@x=1)print '>> ' + REPLICATE('=', 100)
if(@x=1)print '>> @i = ' + Cast(@i as varchar(3)) + ', ' + '@DatabaseName=' + Cast(@DatabaseName as varchar(256)) + ', ' + '@TableName: ' + Cast(@TableName as varchar(256))
-- -------------------------------------
-- Keep SHOWCONTIG results in a table:
-- -------------------------------------
CREATE TABLE ##Dbcc
(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)
SET @s-2 = ''
SET @s-2 = @s-2 + ' USE ' + RTRIM(@DatabaseName)
SET @s-2 = @s-2 + ' DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
INSERT INTO ##Dbcc
EXEC(@s)
SELECT @RecCount = count(*) From ##Dbcc
if(@x=1)print '>> ##Dbcc count = ' + CASE WHEN Cast(@RecCount as Varchar(9)) IS NULL THEN 'NULL' ELSE Cast(@RecCount as Varchar(9)) END
-- -------------------------------------------
-- Get IndexDepth for each index in ##Dbcc
-- -------------------------------------------
CREATE TABLE ##IndexDepth(IndexId INT null, IndexDepth INT null)
DECLARE @_IndexId INT
DECLARE @_IndexName VARCHAR(255)
DECLARE @_TableId INT
SET @_IndexId = (Select Min(IndexId) From ##Dbcc)
-- -----------------
-- traverse ##Dbcc
-- -----------------
WHILE @_IndexId <= (Select Max(IndexId) From ##Dbcc)
BEGIN
SELECT @_IndexName = IndexName,
@_TableId = ObjectId
FROM ##Dbcc
WHERE IndexId = @_IndexId
SET @s-2 = ''
SET @s-2 = @s-2 + ' USE ' + RTRIM(@DatabaseName)
SET @s-2 = @s-2 + ' SELECT ' + Cast(@_IndexId as Varchar(15)) + ', INDEXPROPERTY( ' + Cast(@_TableId as Varchar(15)) + ', ''' + RTRIM(@_IndexName) + ''', ''IndexDepth'') '
INSERT INTO ##IndexDepth
EXEC(@s)
if(@x=1)print '>> @s-2 = ' + @s-2
if(@x=1)print '>> @_IndexId = ' + Cast(@_IndexId as Varchar(100))
if(@x=1)print '>> @_IndexName = ' + Cast(@_IndexName as Varchar(100))
if(@x=1)print '>> @_TableId = ' + Cast(@_TableId as Varchar(100))
SET @_IndexId = (Select Min(IndexId) From ##Dbcc Where IndexId > @_IndexId)
END
-- =================================================
-- Select Indexes with low ScanDensity and Depth > 0
-- MS wants only indexes with Depth more that zero:
-- =================================================
SELECT Identity(int, 1, 1) as RowID, @DatabaseName as DatabaseName, a.ObjectName, a.ObjectId,
a.IndexId, a.IndexName, a.ScanDensity, b.IndexDepth
INTO ##IndexList
-- select *
FROM ##Dbcc as a
INNER JOIN ##IndexDepth as b
ON a.IndexId = b.IndexId
WHERE a.ScanDensity <= 30.0
AND a.IndexId != 0 -- IndexId = 0 for the entire Heap. Not interested.
AND b.IndexDepth > 0 -- Microsoft wants this. See BOL - "DBCC SHOWCONTIG"
ORDER BY ObjectName, IndexName
SELECT @RecCount = count(*) FROM ##IndexList
if(@x=1)print '>> ##IndexList count = ' + CASE WHEN Cast(@RecCount as Varchar(9)) IS NULL THEN 'NULL' ELSE Cast(@RecCount as Varchar(9)) END
-- ========================================================
-- DBCC INDEXDEFRAG
-- ========================================================
SELECT @ii = MIN(RowID) From ##IndexList
WHILE @ii < = (SELECT MAX(RowID) FROM ##IndexList)
BEGIN
SELECT @ObjectId = ObjectId, @IndexId = indexid, @IndexName = IndexName
FROM ##IndexList
WHERE RowID = @ii
SET @s-2 = 'DBCC INDEXDEFRAG( ' + @DatabaseName + ', ' + Cast(@ObjectId as Varchar(15)) + ', ' + Cast(@IndexId as Varchar(15)) + ')'
if(@x=1)print ' >> ~~ begin defrag ~~~~~~~~~~~~~~~~~~~~~~~~~~~'
if(@x=1)print ' >> @s-2 = ' + @s-2
if(@x=1)print ' >> @ii = ' + Cast(@ii as varchar(3)) + ' ' + '@IndexName = ' + Case When @IndexName Is NULL Then 'Nuuull' Else Cast(@IndexName as varchar(256))End
if(@x=1)print ' >> ' + @s-2
EXEC( @s-2 )
/*
-- ==============================================================
-- Log defrag info:
-- ==============================================================
Create table IndexDefragLog( RowId Int Identity(1,1) Not Null, RowDate datetime default Getdate() not null, DatabaseName Varchar(256) null,
TableId Int null, TableName Varchar(256) null, IndexId int null, IndexName varchar(256) null,
IndexDepth int null, ScanDensity decimal(9,2) null, Message varchar(100) null)
*/
INSERT INTO [IntegrityCheck].[dbo].[IndexDefragLog]
(DatabaseName, TableId, TableName, IndexId, IndexName, IndexDepth, ScanDensity)
SELECT DatabaseName, ObjectId, ObjectName, IndexId, IndexName,
'IndexDepth' = (Select IndexDepth From ##IndexDepth Where IndexId = ##IndexList.IndexId ),
ScanDensity
FROM ##IndexList
WHERE RowId = @ii
SET @ii = @ii + 1
END
DROP TABLE ##IndexDepth
DROP TABLE ##IndexList
DROP TABLE ##Dbcc
SET @i = @i + 1
END
DROP TABLE ##TableList
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply