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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy