February 12, 2014 at 8:34 am
In our company we provide an SQL Server 2008 R2 as a testing environment for different employes and departments. Because of the forgetfulness (or laziness) of most of the employees databases don´t get dropped after finishing the tests. So meanwhile there is a great mess on this machine.
I´m tired of running around, calling and mailing everybody to find out wich database is still in use so I decided to write a stored procedure to drop databases in dependece of values in specified tables / columns.
I decided to put the sp_ in master database because the employees can create database without my knowledge. In the sp_ I get a list of all databases with specific characters in their name into a cursor and iterate this. For every database I need to get the value of one column to decide wether to drop this database or not.
If I switch database context with 'USE <DBNAME>' a simple select statement will be executed still on master database. That´s something i knew.
How can I collect the values instead? I tried various Version with temporary tables, table variables, a user defined function in master that was marked as sysobject, ...
Nothing worked for me. I actually don´t want to use another (fix) database to store this informations in.
February 12, 2014 at 8:41 am
Are you building a dynamic sql string in your cursor and then executing that string?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 12:35 am
Have you tried using SP_MSforeachDB ? A vague code using it is written below...Please see if this helps
EXEC sp_MSforeachdb
'
IF EXISTS( SELECT 1 FROM SYS.DATABASES WHERE NAME LIKE ''%LETTER IN DB NAME%'' AND NAME = ''?'' )
BEGIN
SELECT ''?'',CASE
WHEN COLUMN_NAME = VALUE_TOCHECK THEN ''DROP DATABASE ['' + ''?'' + '']''
ELSE ''DB IN USE''
END FROM ?..TABLE_NAME WHERE COLUMN_NAME = VALUE
END
'
February 13, 2014 at 4:05 am
That showed me the right direction. I solved this with the usage of sp_MSforeachdb.
This is the code that works fine for me:
CREATE PROCEDURE [dbo].[CHECK_DATABASES]
AS
BEGIN
SET NOCOUNT ON;
declare@sqlstatement varchar(max)
declare @dbname varchar(100)
IF OBJECT_ID('tempdb..#dbtodel') IS NOT NULL
drop table #dbtodel
create table #dbtodel (dbname varchar(100), todo varchar(300))
EXEC sp_MSforeachdb
'
IF EXISTS( SELECT 1 FROM SYS.DATABASES WHERE NAME LIKE ''FO%'' AND NAME = ''?'' )
BEGIN
insert into #dbtodel
SELECT ''?'',CASE
WHEN upper(PREFS.CVALUE) not like ''\\\\SDBSUPPORT%'' THEN ''DROP DATABASE ['' + ''?'' + '']''
ELSE ''DB IN USE''
END FROM ?..PREFS WHERE PREFS.IVALUE = 6
END
'
delete from #dbtodel where todo = 'DB IN USE'
WHILE EXISTS(select * from #dbtodel)
BEGIN
select top 1 @dbname = dbname, @sqlstatement = todo from #dbtodel order by dbname
execute(@sqlstatement)
delete from #dbtodel where dbname = @dbname
END
END
February 13, 2014 at 8:24 am
The use of sp_MSforeachdb can have unintended results. It will occasionally miss databases. There are better methods (such as a cursor - and considering msforeachdb is just a loop, a cursor is ok).
Here is an article talking about the behavior and a workaround.
Here is a different version of msforeachdb by Gianluca that is what I prefer to use.
http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply