How to collect data from different databases within a stored procedure?

  • 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.

  • 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

  • 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

    '

  • 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

  • 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.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

    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