How To Use Sp_MSforeachdb
Feb 19, 2008
need help-------------
i understand sp_MSforeachdb system stored procedure can be used to do the same task on all the databases. but when i tried this what i does execute that task/command number of times the available databases on same database instead execute that task once on each database.
this is what i was doing
use master
exec sp_msforeachdb @command1 = "exec sp_grantdbaccess @loginame ='test',@name_in_db = 'test'"
GO
can some one ther please help me how i can execute this for each database
View 1 Replies
May 21, 2001
Hey, all...
I'm trying to modify the sp_msforeachdb proc (after copying it and renaming it, of course) to exclude system databases. It could be done in 7.0 by adding a "where name not in ('master', msdb', 'model', 'tempdb')" clause to the select in the cursor, but it doesn't work in the SQL 2000 version.
Does anyone have any ideas? Any insight would be greatly appreciated.
View 2 Replies
View Related
Oct 1, 2007
I have this chunk of code that you enter your table and it tells you what sp, views etc it is in. But it does not show if that table is used in an sp in a different DB. i was told that sp_msForeachdb may help but i have no idea how use it. Help please here is the code i have below
SET nocount ON
DECLARE @string VARCHAR(1000)
--SET @string = 'dbo.RetailSales_ByStore_ByCustomer_ByDay' --> This is your search criteria
SET @string = 'dbo.Store' --> This is your search criteria
DECLARE
@errnum INT,
@errors CHAR(1),
@rowcnt INT,
@output VARCHAR(255)
SELECT
@errnum = 0,
@errors = 'N',
@rowcnt = 0,
@output = ''
DECLARE @Results TABLE (
Name VARCHAR(55),
Type VARCHAR(12),
DateCreated DATETIME,
ProcLine VARCHAR(4000)
)
INSERT
INTO @Results
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscomments SC
ON SC.id = SO.id
WHERE
SC.text LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
WHERE
SO.name LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscolumns SC
ON SC.id = SO.ID
WHERE
SC.name LIKE '%' + @string + '%'
ORDER BY
2,
1
SELECT
Name,
'Type' = CASE (Type)
WHEN 'P' THEN 'Procedure'
WHEN 'TR' THEN 'Trigger'
WHEN 'X' THEN 'Xtended Proc'
WHEN 'U' THEN 'Table'
WHEN 'C' THEN 'Check Constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'K' THEN 'Primary Key'
WHEN 'V' THEN 'View'
ELSE Type
END,
DateCreated
FROM
@Results
ORDER BY
2,
1
View 1 Replies
View Related
May 9, 2006
Hello,
I know that sp_MSForEachDB is not being supported, but I am wondering if someone would help me find out if I can use sp_MSForEachTable within sp_MSForEachDB.
The code below does not work, and I am wondering if the code is feasible.
Error Message - "cursor hCForEach already exist"
declare @var varchar(1000)
set @var =
'EXEC sp_MSForEachTable @command1='INSERT INTO #tblResults2
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused ''?''''
EXEC sp_MSForEachDB @command1=@var
View 4 Replies
View Related
Oct 1, 2007
sp_msforeachdb @command1= 'USE ? exec sp_Depends "dbo.style"'
So im trying to return everything that uses that dbo.style im getting this error I just want to show where that table is being used and suggestions. I know that feature is undocumented. This also doesnt seem to be searching all the Databases when I search it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'master'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'tempdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'msdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'GoldMine_Sales_and_Marketing'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CustomerConcerns'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Aggregate'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLD_BUDGET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CreditCardData'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'COZUM'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet2'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DYNAMICS'.
Object does not reference any object, and no objects reference it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'HUNTB'.
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEV'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEVSmall'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLDOnlineOrdering'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineRMA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineStore'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Reporting'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SSE'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'STTHO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'TMPLT'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'WHAVL'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'QSCHQ'.
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_NET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_NET'.
View 6 Replies
View Related
Mar 13, 2008
I am using SQLExpress for Unit Testing my application. In the Unit Tests, I use a local database file that is attached automatically in SQLExpress when the Unit Test uses it.
FYI, in the Unit Test I use the following connection string :
"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyUnitTestDatabase.mdf;Integrated Security=True;User Instance=False;Pooling=false"
By accident, the MyUnitTestDatabase.mdf file was marked as ReadOnly. So, after executing several times the unit Test, the attached databases appear in grey in the SQL Server Management Studio Express. That's normal ! The problem I want to report here occurs when I execute the following script in SQL Server Management Studio Express:
use master
go
sp_MSForEachDB 'Print ''?'''
go
In .SQLExpress, I currently have 8 databases (The three last databases are those attached by the unit tests. They are ReadOnly):
- master
- model
- msdb
- tempdb
- MyUnitTestDatabase (the original db copied and used by the Unit Tests. It's not ReadOnly)
- 1E6AA4A60F3733D37F016842D4626B8B_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_22_03OUTMYUNITTESTDATABASE.MDF
- ADA9F382DFBC95C8334EF95336C98274_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_13_57OUTMYUNITTESTDATABASE.MDF
- F00BF38C8BB8F07D37FCC4E918CF815E_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_10_04OUTMYUNITTESTDATABASE.MDF
When executed, the script mentioned above displays sometimes all the databases and sometimes only the 4 first databases ?!?!
I did a demo to various colleagues here, pressing F5 many times in the Script windows. It's seems taht it displays 4 names or 8 names "at random"... (I always wait for the message "Query executed successfully" before pressing again F5).
I have to understand the problem here because I use sp_MSForEachDB to detach all the databases at the end of the Unit Tests and it also fails from time to time...
Thx in advance for any tip that could help me in finding the origin of this problem
V.
PS. : FYI, here is the stored proc I use to automatically detach the databases at the end of the unit tests
declare @spid int
declare @killstatement nvarchar(10)
IF @database like '%TESTRESULTS%'
BEGIN
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
from sys.dm_tran_locks
where resource_type='DATABASE' AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(3))
exec sp_executesql @killstatement
end
fetch next from c1 into @spid
end
close c1
deallocate c1
exec msdb.dbo.sp_delete_database_backuphistory @database
exec master.dbo.sp_detach_db @database, 'true'
END
END
Possibly I was not detaching the databases in a "clean way" and my system databases are now corrupted ? Is such a case, what should I do in addition to the code here above to correctly detach the databases ?
View 3 Replies
View Related
