sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) dbo.index_name(object_id, index_id) AS ixNameįROM. I have included the output at the bottom.ĭECLARE varchar(50), varchar(50), int, nvarchar(500), nvarchar(500) The purpose of the script is to reorg indexes that are fragmented between 5 and 30 percent. Yes, this is a cursor, I realize you are all going to 'smack' me on that also. 3) I have also tried putting the script into a variable and running the exec sp_msforEachDB What am I doing wrong? I am testing the script as a print to be sure it is working before I run the execute. 2) when the script executes, it only seems to get a few of the databases and not all of them. 1) although, I have the filter on the databases excluding master, model, msdb, tempdb, it is still executing the script in them. I am using the sp_msForcEachDB and it is having difficulities. It is returning the database name in loop but for all databases file name and size of the file is master.msd and master.ldf. Your cursor script Is giving the output wrongly. My user, login, password are same for all the DB's I have 50 databases in my server and i want to run this query on all databases.ĮXEC sp_change_users_login 'Auto_Fix', ' XXXX ', ' XXXX ', ' XXXX ' I was going through the code of sp_MSforeachdb and found that it does work on cursors internally.īasically, this SP opens the global cursor which will be used by the SP (worker SP - sys.sp_MSforeach_worker - called from current SP). I found something interesting while reading this post so thought to share. I've found that USE ? sometimes is failing when database name contains -, so I would like recommend to use Sp_MSforeachdb is a cursor! :) Look at the codeĮxec sp_helptext 'sys.sp_MSforeach_worker' SELECT = 'IF ''?'' NOT IN(''master'') BEGIN USE ?ĮXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END' the databiles are all for the master database and not each database in the loop. The code in these demonstrations don't actually run the queries against the selected databases. I also added some code to the cursor since it was not changing databases. The author was trying to show if the database context does not change it will query the same database. I just tried with SQL Server 2022 and they are working.Įxample 3b does not change the output because it does not include "USE ?". Wednesday, Janu2:01:13 PM - Greg Robidoux So in example 3 we get the correct output since we are using USE ?Īnd then we use ''?'' to return the actual database name in the Would execute the T-SQL text of the 5 times in DBx. You were to run the stored procedure code above while in the context of DBx it If you have 5 databases hosted in the current instance and To run against, by using the USE ? statement, otherwise theĬode will execute in the context of the current database, for each database in It is necessary to set the database for the query To reference the database nameĪs a string to be returned in a query, it needs to be embed between aĭouble set of single quotation marks. Throughout the examples provided above you saw the use of the question markĪs a placeholder for the database name. Example 3: Query File Information From All Databases On A SQL Instance To accomplish this you can encapsulate the CREATE PROCEDURE code within an explicitĮXEC() function. Procedure, the CREATE PROCEDURE phrase must be the first line of code to be executed. In this case the rule that when creating a USE ? statement is contained within the BEGIN.END block. Is true by using the T-SQL keywords BEGIN and END. You must also set the code to execute if the IF statement Limiting the scope of the sp_MSforeachdb stored procedure, particularly when creating SELECT = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?ĮXEC(''CREATE PROCEDURE spNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'ĮXEC sp_MSforeachdb you may notice, there are additional items to take into consideration when This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification date
0 Comments
Leave a Reply. |