April 8, 2007 at 5:56 pm
Ok guys I am trying to get this script to run on a server and grab all the database names and then return the tables and the row count with size here is the SP I am using and then after it is a cursor that i am using to exe the SP.
USE
[master]
GO
/****** Object: StoredProcedure [dbo].[usp_dbtablesize] Script Date: 04/03/2007 13:42:55 ******/
IF
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dbtablesize]') AND type in (N'P', N'PC'))
DROP
PROCEDURE [dbo].[usp_dbtablesize]
GO
USE
[master]
GO
/****** Object: StoredProcedure [dbo].[usp_dbtablesize] Script Date: 04/03/2007 09:43:38 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
--@include_system_tables = 1 includes (0 leaves them out)
CREATE
PROC [dbo].[usp_dbtablesize]
(
@DBName varchar(100) )
--(
--@top int = NULL,
--@include_system_tables bit = 0
--)
AS
BEGIN
DECLARE
@sql varchar(8000)
/*********************************************************
***********************************************************/
DECLARE
@top int, @include_system_tables bit
SET
@top=NULL
SET
@include_system_tables = 0
/************************************************************/
-- IF @top > 0
-- SET ROWCOUNT @top
SET
@sql = 'SELECT ''' + @DBName + ''' +''.''+ [Table Name] AS TableName, (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + ''.'' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = ''E'')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM '
+ @DBName + '.dbo.sysindexes i (NOLOCK)
INNER JOIN
'
+ @DBName + '.dbo.sysobjects o (NOLOCK)
ON
i.id = o.id AND
(('
+ CONVERT(VARCHAR,@include_system_tables) + '= 1 AND o.type IN (''U'', ''S'')) OR o.type = ''U'') AND
(('
+ CONVERT(VARCHAR,@include_system_tables) + '= 1)OR (OBJECTPROPERTY(i.id, ''IsMSShipped'') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + ''.'' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0 '
EXEC
( @sql)
END
This is the Cursor I run. It only returns values for the master DB it shows null for all other databases on the server.
--insert this text into the job step (confirm sp is created)
DECLARE
@DBName varchar(100)
DECLARE
@sql varchar(1000)
DECLARE
@rc int
DECLARE
cur_db CURSOR FOR
SELECT [name] FROM SYS.DATABASES
OPEN
cur_db
FETCH
NEXT FROM cur_db INTO @DBName
WHILE
@@FETCH_STATUS = 0
BEGIN
EXECUTE @rc = [master].[dbo].[usp_dbtablesize] @DBName
FETCH NEXT FROM cur_db INTO @DBName
END
CLOSE
cur_db
DEALLOCATE
cur_db
April 9, 2007 at 6:05 am
Still doesnt work. It looks like its getting the correct number of databases but it only populates data from the master database. The others all come up with no data. Thanks for the help.
April 9, 2007 at 9:34 am
What about using sp_msforeachdb along the lines of
'SELECT [TABLE_NAME]
FROM ?.INFORMATION_SCHEMA.TABLES'
Substitute where you need to. This works across all the database and eliminates the need for a cursor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2007 at 3:01 pm
The OBJECTPROPERTY function works only in the current database, so it's crossing IDs. You can either avoid using that, or alternatively, you could build a 'USE ' + @dbname + 'GO ' type dynamic SQL string in your cursor.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply