SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_MSForeachdb then sp_MSforeachtable


sp_MSForeachdb then sp_MSforeachtable

Author
Message
tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6676 Visits: 5316
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.
mtassin
mtassin
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8496 Visits: 72521
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
tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6676 Visits: 5316
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.
mtassin
mtassin
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8496 Visits: 72521
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
tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6676 Visits: 5316
Agreed. Thanks for your help.

-- You can't be late until you show up.
Prakash Heda
Prakash Heda
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1494 Visits: 416
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
davisilmail
davisilmail
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85167 Visits: 9671
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


jared 5697
jared 5697
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 80
@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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85167 Visits: 9671
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search