Thanks for your wonderful scripts!

You save my time and money!

Have a great day!

nice script ! thanks a bunch!

 Thanks so much. 

Tim Ford did a great job with this tip.

Thank you,
The MSSQLTips.com Team

You may need some "[" and "]" around the '?' to deal with dodgy database names.......

EXEC sp_MSforeachdb 'USE [?] SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'

R

You are absolutely correct.  This is particularly important when dealing with spaces in database names.

 
EXEC sp_msforeachdb 'USE ?; if object_id(''sp_StoredProc1'') is not null begin print ''?'' GRANT EXECUTE ON [dbo].[sp_StoredProc1] TO public end'

is there a way to have a variabled declared  withing the statement?

You need to replace

     SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
with

     SELECT @Command = 'USE '+ @DB_NAME +  ' SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'

when you want to execute by cursor otherwise it gives you all file name as master.mdf

There are times when you might need a cursor:

1) running a script against certain databases, instead of all

2) wanting your script results in one table (for a select, for example)

When you need a special condition of list of DBs such as not offline, not read-only, use temp table or CTE or table variable, for example, the code below

Regards,

Jason

http://usa.redirectme.net

-----------------

SET NOCOUNT ON

DECLARE @dbName varchar(255), @rc int

SELECT name
INTO #db
FROM sys.databases
WHERE
    name NOT IN ('master', 'model', 'msdb', 'tempdb', 'admindb') AND
    name NOT LIKE '%ReportServer%' AND
    DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
    DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'

SELECT @rc = 1, @dbName = MIN(name)
FROM #db

WHILE @rc <> 0
BEGIN

    EXEC DB.schema.DO_SOMETHING

    SELECT TOP 1 @dbName = name
    FROM #db
    WHERE name > @dbName
    ORDER BY name

    SET @rc = @@ROWCOUNT
END

DROP TABLE #db

There is anothers parameters to comments... EXEC sp_msforeachdb sintax accepts

EXEC sp_msforeachdb @precommand='---YOUR TEXT FOR GENERAL PURPUSE HERE-TEMP TABLE CREATION IE...

CREATE TABLE ##TEMP1 (ID INT,NAME VARCHAR(15);SELECT * INTO ##TEMP2 FROM ##TEMP1',

@COMMAND1='USE [?];SELECT * INTO ##TEMP1 WHERE ...',

@COMMAND2='USE [?];SELECT * INTO ##TEMP2 WHERE ...',

 @COMMAND3='USE [?];SELECT * INTO ##TEMP1 WHERE ...',

@POSTCOMMAND='SELECT * FROM ##TEMP1 UNION SELECT * FROM ##TEMP2;DROP TABLE ##TEMP1;DROP TABLE ##TEMP2;'

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name FROM master..sysdatabases where name like '%_Se'

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Command = 'USE ['+ @DB_NAME +  '] INSERT INTO  genMenuMaster
                    (MenuId, ModuleId, MenuName, MenuType, ParentId, MenuURL, [Position])
VALUES     (1163, 32, ''SMS Mobile DND Status Report'', 0, ''General/SMSMobileDNDStatus.aspx'', 60)'
   EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Server: Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark before the character string '\General/S'.

Respect sir

             i am facing some problem in sql query  using cursor declaration t was try to do with declare variable

              declare

cmain cursorforselect*from gstock

                open

cmain

             how to use cursor in fetch status...

Dear sir

      I AM FACING SOME PROBLEM IN SQL QUERY CURSOR DECLARATION I WANT TO USE CURSOR WITHOUT DECLARE VARIABLE

    PLEASE GIVE ME SOLUCTION

   MENTON MY QUERY

    DECLARE @ABCTABLE AS TABLE  (STKCODE INT,STOCKNO VARCHAR(20))

    declare abc_cursor cursor for select  stkcode ,stockno from stock   

   OPEN abc_curosr                ** THIS QUERY EXECUTE  SUCESSFUL BUT HOW TO USE IN FETCH NEXT FROM CURSOR

    FETCH NEXT FROM abc_curosr  INTO @stkcode,@stockno      ** ERROR CURSOR VARIABLE NOT READ

         WHILE @@FETCH_STATUS = 0
           BEGIN

              INSET INTO  @ABCTABLE (STKCODE,STOCKNO) VALUES (@stkcode,@stockno)

            FETCH NEXT FROM abc_curosr       INTO  @DB_Name
END

CLOSE abc_curosr      
DEALLOCATE abc_curosr      

This, and sp_msforeachtable, are internally not magic or undocumented, in that they don't invoke any CLR functionality, extended stored procs, obfuscated TSQL, etc.

They're just plain ol' TSQL procs that executes the code specified in the parameters inside a CURSOR that loops over the desired objects...

Hi,

I got your email id in forum.
I am having issues in SQL,
Issues is that I am having more than 50 customers and there is more than 50 Database in SQl for every customers.
Now All SP's And Functions are same but now problem is in maintenance, If I modify any SP then I need to modify SP manually in all database.
So is there any way to avoid such maintenance problem  So I can modify one SP and that will get update in all remaining Database?

Or Can I get script in c# code using the can i modify SP in all database?
EXEC sp_MSforeachdb can not work for me because some of my SP text length is more than 10,000.

Kindly help me.. Thanks in Advance.

Hi,

I use this script to fix all Orphan users in a Server

(Use a cursor, because requires to check for all the users in the database)

exec sp_msforeachdb '
        USE [?];
       
        DECLARE
            @DB sysname
           
        SELECT DB_NAME()
       
        BEGIN
            BEGIN
                DECLARE
                    @username varchar(25)
               
                DECLARE fixusers CURSOR FOR
                    SELECT UserName = name
                    FROM sysusers
                    WHERE issqluser = 1
                        AND (sid is not null and sid <> 0x0)
                        AND name <>''dallas''
                        AND suser_sname(sid) is null
                    ORDER BY name
                   
                    OPEN fixusers
                        FETCH NEXT FROM fixusers
                            INTO @username
                           
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                EXEC sp_change_users_login ''update_one'', @username, @username
                                FETCH NEXT FROM fixusers
                                    INTO @username
                            END   
                    CLOSE fixusers
                DEALLOCATE fixusers
            END
                       
        END'

Thanks for the tip

Regards

Hi,

I also use a simple script (Callit from a Job) to check for the status of all the databases.

Runs after a Failover or a Server Reboot.

CREATE PROC [dbo].[usp_CheckDB]
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
            exec sp_msforeachdb '
                use [?];
                exec sp_helpdb ?';
       
    END TRY
    ---
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Use RAISERROR inside the CATCH block to return error
        -- information about the original error that caused
        -- execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
    END CATCH
    COMMIT
END

Nice!  I use this to shrink db and truncate logs of EVERY DB.

DECLARE @cmd1 nvarchar(2000)

SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'')' + 'BEGIN ' 

+ 'USE ?

DBCC SHRINKFILE( ?_log, 1 )

BACKUP LOG ? WITH TRUNCATE_ONLY

DBCC SHRINKFILE( ?_log, 1 )

END

' 

EXEC sp_MSForEachdb 

@command1 = @cmd1 

GO

Very useful indeed

Thanks a lot man ! You save my day.

Regards,

Jordan

I need to loop the following script through all databases.  I have tried almost everything I have researched online without any success.  Any suggestions?

DECLARE @table_name VARCHAR(500)  

DECLARE @schema_name VARCHAR(500)  

DECLARE @tab1 TABLE( 

        tablename VARCHAR (500) collate database_default 

       ,schemaname VARCHAR(500) collate database_default 

) 

CREATE TABLE #temp_Table ( 

       [tablename] [sysname] NOT NULL,

[row_count] [int] NULL,

[reserved] [varchar](50) NULL,

[data] [varchar](50) NULL,

[index_size] [varchar](50) NULL,

[unused] [varchar](50) NULL)  

INSERT INTO @tab1  

SELECT Table_Name, Table_Schema  

FROM information_schema.tables  

WHERE TABLE_TYPE = 'BASE TABLE' 

DECLARE c1 CURSOR FOR 

SELECT Table_Schema + '.' + Table_Name   

FROM information_schema.tables t1  

WHERE TABLE_TYPE = 'BASE TABLE' 

OPEN c1 

FETCH NEXT FROM c1 INTO @table_name 

WHILE @@FETCH_STATUS = 0  

BEGIN   

        SET @table_name = REPLACE(@table_name, '[','');  

        SET @table_name = REPLACE(@table_name, ']','');  

        -- make sure the object exists before calling sp_spacedused 

        IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) 

        BEGIN 

               INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;

                     END 

        FETCH NEXT FROM c1 INTO @table_name 

END 

CLOSE c1 

DEALLOCATE c1 

SELECT  t1.* 

       ,t2.schemaname  

FROM #temp_Table t1  

INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) 

ORDER BY schemaname,t1.tablename; 

ALTER TABLE #temp_Table ADD [DATE] DATE

UPDATE #temp_Table SET [DATE] =  CURRENT_TIMESTAMP

AlteR TABLE #temp_Table ADD [DATABASE] varchar (100)

UPDATE #temp_Table SET [DATABASE] = DB_NAME() 

CREATE TABLE #temp_Table_test (name varchar(100),

data_compression tinyint,

data_compression_desc varchar(100))

insert into #temp_Table_test SELECT T.name, P.data_compression, P.data_compression_desc 

 FROM SYS.TABLES T, SYS.partitions P

WHERE T.OBJECT_ID = P.OBJECT_ID

GROUP BY T.name, P.data_compression, P.data_compression_desc

ALTER TABLE #temp_Table ADD [COMPRESSED] VARCHAR (50)

UPDATE #temp_Table 

set [COMPRESSED] = b.data_compression_desc

from #temp_Table a,

#temp_Table_test b

where a.tablename = b.name

----------------------------------------------

insert into bic_reference.dbo.test_database_stats  

select * from #temp_Table

DROP TABLE #temp_Table

Drop Table #temp_Table_test

I need assisance, please.  I am using the sp_msForcEachDB and it is having difficulities.  1) although, I have the filter on the databases excluding master, model, msdb, tempdb, it is still executing the script in them.  2) when the script executes, it only seems to get a few of the databases and not all of them.  3) I have also tried putting the script into a variable and running the exec sp_msforEachDB @command  What am I doing wrong?  I am testing the script as a print to be sure it is working before I run the execute.  Yes, this is a cursor, I realize you are all going to 'smack' me on that also.    The purpose of the script is to reorg indexes that are fragmented between 5 and 30 percent.  Here is my script... I have included the output at the bottom. 

EXEC

sp_MSforeachdb

'IF ''[?]'' NOT IN (''master'',''tempDB'',''model'',''msdb'')

BEGIN

use [?]

DECLARE @dbName sysname,@table varchar(50), @index varchar(50), @frag int, @msg varchar(500),

@sql nvarchar(500), @dbsql nvarchar(500)

declare index_cur cursor for

SELECT OBJECT_NAME(object_id) AS tblName

,[?].dbo.index_name(object_id, index_id) AS ixName

,avg_fragmentation_in_percent

FROM [?].sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)

WHERE avg_fragmentation_in_percent between 5 and 30

and index_type_desc IN(''CLUSTERED INDEX'', ''NONCLUSTERED INDEX'')

open index_cur

FETCH NEXT FROM index_cur into @table, @index, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

if objectproperty (object_id (@table), ''OwnerId'') = 1

set @sql = N''ALTER INDEX '' + quotename(@index) + '' ON '' + ''[dbo].''+ quotename(@table)

+ '' REORGANIZE''

print @sql

FETCH NEXT FROM index_cur into @table, @index, @frag

END

CLOSE index_cur

DEALLOCATE index_cur

END'

Msg 4121, Level 16, State 1, Line 9

Cannot find either column "tempdb" or the user-defined function or aggregate "tempdb.dbo.index_name", or the name is ambiguous.

Msg 4121, Level 16, State 1, Line 9

Cannot find either column "model" or the user-defined function or aggregate "model.dbo.index_name", or the name is ambiguous.

Msg 4121, Level 16, State 1, Line 9

Cannot find either column "msdb" or the user-defined function or aggregate "msdb.dbo.index_name", or the name is ambiguous.

ALTER INDEX [IX_bs_unitanks_FloatTable_DateAndTime] ON [dbo].[BS_UNITANKS_FloatTable] REORGANIZE

ALTER INDEX [IX_beerfilter_FloatTable_DateAndTime] ON [dbo].[BeerFilter_FloatTable] REORGANIZE

ALTER INDEX [XPKItem] ON [dbo].[Item] REORGANIZE