March 28, 2008 at 3:28 pm
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.
April 2, 2008 at 2:23 pm
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
April 2, 2008 at 2:33 pm
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.
April 2, 2008 at 2:38 pm
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.
April 2, 2008 at 2:57 pm
Agreed. Thanks for your help.
-- You can't be late until you show up.
August 10, 2009 at 8:12 pm
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
August 3, 2010 at 5:01 am
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.
July 13, 2011 at 3:58 pm
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
January 7, 2013 at 4:52 pm
@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
January 7, 2013 at 6:29 pm
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.
July 19, 2013 at 6:00 am
To get dB size with Path for each dBs on server.
SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs,
PHYSICAL_NAME
FROM master.sys.master_files mf
INNER JOIN master.sys.databases d ON d.database_id = mf.database_id
WHERE mf.database_id > 4 -- Skip system databases
GROUP BY d.name, PHYSICAL_NAME
ORDER BY d.name
October 29, 2014 at 1:16 pm
YOU CAN TRY WITH THIS
CREATE PROCEDURE usp_DataRowCounts AS
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
EXEC sp_MSForeachdb @replacechar='%', @command1= " USE %
IF DB_ID('%')>4 begin
INSERT TEST_table
EXEC sp_msforeachtable 'sp_spaceused ''?'''
end"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply