dynamic USE stmt

Last Post 19 Mar 2008 11:37 AM by gilou28. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gilou28
New Member
New Member

--
19 Mar 2008 04:21 AM
Is there a way, in TSQL, to issue a USE statement with a variable ?

I tried:

quote:


declare @dbname varchar(100)
set @dbname = 'DBATools'

declare @dbid int
set @dbid = 5 -- test db

declare @stmt varchar(100)
set @stmt = 'USE ' + @dbname


-- USE @dbname
--> Incorrect syntax near '@dbname'.

-- USE DB_NAME(@dbid)
--> Could not locate entry in sysdatabases for database 'DB_NAME'. No entry found with that name. Make sure that the name is entered correctly.

--EXEC(@stmt)
--> Command(s) completed successfully. BUT db NOT changed




Thanks.
gilou28
New Member
New Member

--
19 Mar 2008 06:09 AM
Thansk for your reply Russellb

Is there a way to change the context of the currently logged session then ? (in TSQL)

gilou28
New Member
New Member

--
19 Mar 2008 07:54 AM
I have a script that rebuil/reorganise indexes according to their fragmentation. This script is called from the O/S using sqlcmd (I'm connecting to different instances on different servers, all from one central server)

Here is the code:

quote:


........

INSERT INTO @AllIndexes
SELECT object_id, index_id, avg_fragmentation_in_percent, DB_NAME(database_id)
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, 'LIMITED')
WHERE database_id > 4 -- Seulement BD Usagers
AND index_id > 0
ORDER BY database_id

OPEN indexesToDefrag;
FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag, @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @schemaname = s.name
FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid

SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid

IF @frag > @onlineDefragThreshold
BEGIN
SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid)
IF @frag < @offlineDefragThreshold SET @command = @command + ' REORGANIZE' ELSE SET @command = @command + ' REBUILD'
BEGIN TRY
EXEC (@command)
END TRY
BEGIN CATCH
SET @errmsg = ERROR_MESSAGE()
RAISERROR(@errmsg, 16, 127)
END CATCH

SELECT 'ALTER INDEX exécutée: ' + @command as alter_index
END

IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
BEGIN
SET @command = 'UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
BEGIN TRY
EXEC (@command)
END TRY
BEGIN CATCH
SET @errmsg = ERROR_MESSAGE()
RAISERROR(@errmsg, 16, 127)
END CATCH
SELECT 'UPDATE STATISTICS exécuté: ' + @command
END

FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag, @dbname
END

...................




The initial SELECT (the one that populate the temp table, is done against the MASTER db to retrieve all indexes on that instance.

Then the two SELECTs in the loop will return the schemaname and indexname from the sys.objects and sys.indexes tables that have a scope limited to the current DB.

So, in the loop, I'd like to issue a USE statement according to the @dbname variable coming from the temp table.

I have tried to make one single select to populate the temp table with all the information in one select, but because of the scope issue of sys.objects and sys.indexes tables, I'm a bit stuck.

You now have the whole idea :-)


gilou28
New Member
New Member

--
19 Mar 2008 11:37 AM
Yep, this whould do the job for the ALTER statement, but I have to try for the 2 SELECT that assign local variable values.

For the SSMS window, the context of the windows (connection) does not change.

UPDATE: I changed the use of the EXEC @cmd to SP_EXECUTESQL. Much better and flexible with parameters (in and out).

Thanks,
You are not authorized to post a reply.

Acceptable Use Policy