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