Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_MSForeachdb then sp_MSforeachtable Expand / Collapse
Author
Message
Posted Friday, March 28, 2008 3:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm sure it's missing a quote (or has one too many) but I'm a little brain dead at the moment. Server moves this weekend so I know I won't have time to fool with this until Monday, if all goes well. If anyone fixes this or points me in the right direction, thanks. The table schema is below as well. Have a good weekend.


CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4
BEGIN

INSERT INTO DBMaint..tblTableData
EXEC sp_MSforeachtable 'sp_spaceused ''?''

END'
GO



CREATE TABLE tblTableData
(vchTableName VARCHAR(100) NOT NULL,
intRows INT NOT NULL,
vchReservedSpace VARCHAR(100) NOT NULL,
vchData VARCHAR(100) NOT NULL,
vchIndexSize VARCHAR(100) NOT NULL,
vchUnusedSpace VARCHAR(100) NOT NULL)


-- You can't be late until you show up.
Post #476475
Posted Wednesday, April 2, 2008 2:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
tosscrosby (3/28/2008)
Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm sure it's missing a quote (or has one too many) but I'm a little brain dead at the moment. Server moves this weekend so I know I won't have time to fool with this until Monday, if all goes well. If anyone fixes this or points me in the right direction, thanks. The table schema is below as well. Have a good weekend.

CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4
BEGIN

INSERT INTO DBMaint..tblTableData
EXEC sp_MSforeachtable 'sp_spaceused ''?''

END'
GO



CREATE TABLE tblTableData
(vchTableName VARCHAR(100) NOT NULL,
intRows INT NOT NULL,
vchReservedSpace VARCHAR(100) NOT NULL,
vchData VARCHAR(100) NOT NULL,
vchIndexSize VARCHAR(100) NOT NULL,
vchUnusedSpace VARCHAR(100) NOT NULL)



My first best guess would be that you're using the same substitution character for both ms_foreachdb and ms_foreachtable. ms_foreachdb will replace all the ?'s contained in it's query with the database name.

However, after correcting that and checking over and over, I'm thinking the USE statement is pissing off the whole process. Since sp_msforeachdb creates a cursor anyways, why not make your own? You can do more that way.


Try this
CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
declare csr_db cursor for
select [name] from master..sysdatabases
where [dbid] >4

declare @cmd varchar(max)

declare @dbs varchar(256)
open csr_db
fetch next from csr_db into @dbs
while @@fetch_status = 0
BEGIN
set @cmd = @dbs + '..sp_msforeachtable '' insert into DBMaint..tblTabledata(vchtablename,introws,vchreservedspace,vchData,vchIndexSize,vchUnusedSpace) exec sp_spaceused ''''?'''' '''
exec(@cmd)
set @cmd = 'update DBMaint..tblTabledata set vchdatabasename = ''' + @dbs + ''' where vchdatabasename is null'
exec(@cmd)
fetch next from csr_db into @dbs
END
close csr_db
deallocate csr_db

I changed the table structure to also include the database name.

CREATE TABLE tblTableData
(vchdatabasename varchar(256) NULL,
vchTableName VARCHAR(100) NOT NULL,
intRows INT NOT NULL,
vchReservedSpace VARCHAR(100) NOT NULL,
vchData VARCHAR(100) NOT NULL,
vchIndexSize VARCHAR(100) NOT NULL,
vchUnusedSpace VARCHAR(100) NOT NULL)

Substitute varchar(8000) for varchar(max) in non SQL2k5 systems




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #478836
Posted Wednesday, April 2, 2008 2:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
I have something similar to what you've created (I'll admit, your's is better looking!) using two cursors, one for the database and then one for the table names.
Then I kept reading about the undocumented stored procs and also about how cursors are BAD and started to fool around with the FOREACH sprocs. Didn't think about reusing the same substitution characters. I bet that's a huge part of my problem. Is any substitution characther valid? I appreciate the response. Thanks.


-- You can't be late until you show up.
Post #478842
Posted Wednesday, April 2, 2008 2:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
You can use whatever you like for the substitution character, but if you use something other than ? you need to specify it with a second parameter to the sp_msforeachdb or sp_msforeachtable sp with the parameter @replacechar.

sp_helptext 'sp_msforeachdb'
or
sp_helptext 'sp_msforeachtable'

However, each of the sp_msforeach stored procs creates a cursor anyway and you're stuck with the capabilities programmed into MS's undocumented SPs. By writing your own outer loop via your own cursor instead of MS's... you can slip in the database name, which you'll probably want.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #478845
Posted Wednesday, April 2, 2008 2:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
Agreed. Thanks for your help.

-- You can't be late until you show up.
Post #478853
Posted Monday, August 10, 2009 8:12 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:32 AM
Points: 372, Visits: 414
try this...

declare @command1 varchar(8000)

select @command1 =

'
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb''
begin
use [@] execute sp_MSForEachTable ''print ''''?'''' ''



end
'



exec sp_MSforeachdb @command1, '@'


Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Post #768327
Posted Tuesday, August 3, 2010 5:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 8:46 AM
Points: 1, Visits: 83
Just to mention that if the database name contains - like test-test then the script will fail.
The database name needs to be enclosed between square brackets.
I suggest this modified version

USE [TempPAST]
GO
/****** Object: StoredProcedure [dbo].[usp_DataRowCounts] Script Date: 08/03/2010 12:22:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_DataRowCounts] AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
declare csr_db cursor for
select [name] from master..sysdatabases
where [dbid] >4

declare @cmd varchar(max)

declare @dbs varchar(256)
declare @dbs2 varchar(256)
open csr_db
fetch next from csr_db into @dbs
while @@fetch_status = 0
BEGIN
set @dbs2 = '[' + @dbs + ']'
set @cmd = @dbs2 + '..sp_msforeachtable '' insert into TempPAST..tblTabledata(vchtablename,introws,vchreservedspace,vchData,vchIndexSize,vchUnusedSpace) exec sp_spaceused ''''?'''' '''
print @cmd
exec(@cmd)
set @cmd = 'update TempPAST..tblTabledata set vchdatabasename = ''' + @dbs2 + ''' where vchdatabasename is null'
print (@cmd)
exec(@cmd)
fetch next from csr_db into @dbs
END
close csr_db
deallocate csr_db

Thanks,

Davide.
Post #962709
Posted Wednesday, July 13, 2011 3:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
I just had to have fun with this.

I had to try like 15 different things to whack any sort of loop. Anyhow this is really nice and usable. Enjoy :

USE master
GO
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];

IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''

INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd

UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'

--PRINT @Cmd

--EXEC sp_MSforeachtable @Cmd
EXEC sp_MSforeachdb @Cmd


SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
WHERE Row_count > 0
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC

COMMIT

Post #1141408
Posted Monday, January 7, 2013 4:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:22 AM
Points: 3, Visits: 47
@Ninja's_RGR'us: I really like that solution and have already been putting it to use, but I'm trying to make one slight modification and am having some difficulties. Hopefully someone can help me out here.

What I'd like to do is use that same solution but find a way to have it detect all linked servers and run the same thing on all the linked servers as well. Unfortunately when using these stored procedures, any variables passed in are only good for the duration of the procedure and we can't pass a variable for the linked server name unless we declare and define it within the same variable that we are passing to sp_MSforeachdb (@Cmd in this case). I know this is do-able but I'm having trouble doing it properly. Any help would be much appreciated.

Thanks!

-jared
Post #1403914
Posted Monday, January 7, 2013 6:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
Never done that and I don't think this script is the way to go for that.


If you start a new thread you'll get a tone more help.

Good search.
Post #1403942
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse