May 23, 2003 at 12:06 am
The question is such: why this stored procedure does not count records for some of the databases, sometimes most on a server. (I guess it has something to do with database options)
======================================
CREATE PROCEDURE usp_get_record_counts AS
-- Written by: Greg Larsen Date: 2/28/22002
-- All rights reserved Copyright 2002
declare @CMD char(2000)
declare @DB varchar(100)
-- don't need counts to be displayed
set nocount on
-- define cursor to hold list of databases
declare db cursor for
select name from master..sysdatabases
-- Open database cursor
open db
-- get first database
fetch next from db into @db
-- Process until no more databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print header
print 'Record Counts for database ' + @DB
-- Build command to get the record counts for current database
set @CMD = 'SELECT ' + char(39) + @DB + char(39) +
'as DB, rows, b.name FROM ' +
@DB + '..sysindexes a , ' + @DB + '..sysobjects b ' +
'WHERE a.id = b.id and type=''u'''+
'AND indid < 2 order by b.name'
-- Process command to return record counts
exec (@CMD)
-- get next database
fetch next from db into @db
end
-- Clean up
close db
deallocate db
======================================
The output looks like:
======================================
Record Counts for database MANAGE
DB,rows,name
MANAGE,8,BackupDatabases
Record Counts for database master
DB,rows,name
master,0,dtproperties
master,38940,locks
master,2,MSreplication_options
master,199626,proc_use
master,199565,proc_use2
master,36,spt_datatype_info
master,10,spt_datatype_info_ext
master,0,spt_fallback_db
master,0,spt_fallback_dev
master,0,spt_fallback_usg
master,1,spt_monitor
master,25,spt_provider_types
master,29,spt_server_info
master,728,spt_values
master,78130,trace1
master,110904,trace2
master,195519,trace3
master,206398,trace4
master,199700,trace5
master,199502,trace6
Record Counts for database mbsII
DB,rows,name
Record Counts for database model
DB,rows,name
=======================================
May 23, 2003 at 12:21 am
worked fine for me -
didnt return records for tempdb and model- because it doesnt have any user tables.
May 23, 2003 at 12:34 am
My mbsII database is a large DB (~7Gb), has lots of user tables. It returned:
"Record Counts for database mbsII
DB,rows,name"
It works so for most of my databases 🙁
May 23, 2003 at 6:26 am
If it may help, this is results for sp_configure for the server and create statement for the database:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 1792 1792
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65536 6144 6144
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 1 1
remote query timeout (s) 0 2147483647 7200 7200
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 1056 1056
=================================================================================
CREATE DATABASE [mbsII] ON (NAME = N'mbsii_data', FILENAME = N'D:\sql_data\mbsii_data.mdf' , SIZE = 6600, FILEGROWTH = 200) LOG ON (NAME = N'mbsii_log', FILENAME = N'D:\sql_log\mbsii_log.ldf' , SIZE = 462, FILEGROWTH = 100)
COLLATE SQL_Latin1_General_CP1251_CS_AS
GO
exec sp_dboption N'mbsII', N'autoclose', N'false'
GO
exec sp_dboption N'mbsII', N'bulkcopy', N'false'
GO
exec sp_dboption N'mbsII', N'trunc. log', N'false'
GO
exec sp_dboption N'mbsII', N'torn page detection', N'true'
GO
exec sp_dboption N'mbsII', N'read only', N'false'
GO
exec sp_dboption N'mbsII', N'dbo use', N'false'
GO
exec sp_dboption N'mbsII', N'single', N'false'
GO
exec sp_dboption N'mbsII', N'autoshrink', N'false'
GO
exec sp_dboption N'mbsII', N'ANSI null default', N'true'
GO
exec sp_dboption N'mbsII', N'recursive triggers', N'false'
GO
exec sp_dboption N'mbsII', N'ANSI nulls', N'false'
GO
exec sp_dboption N'mbsII', N'concat null yields null', N'false'
GO
exec sp_dboption N'mbsII', N'cursor close on commit', N'false'
GO
exec sp_dboption N'mbsII', N'default to local cursor', N'false'
GO
exec sp_dboption N'mbsII', N'quoted identifier', N'false'
GO
exec sp_dboption N'mbsII', N'ANSI warnings', N'false'
GO
exec sp_dboption N'mbsII', N'auto create statistics', N'true'
GO
exec sp_dboption N'mbsII', N'auto update statistics', N'true'
GO
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'mbsII', N'db chaining', N'false'
GO
May 28, 2003 at 11:51 pm
Well, wrote a code, that works fine, can count number of records and size of all tables, even system (just change the type of the object):
print GetDate()
print '------------------------------------------------'
DECLARE @table_name varchar(100)
DECLARE table_names CURSOR
LOCAL
SCROLL
STATIC
READ_ONLY
FOR
SELECT [name] FROM [mbsII].[dbo].[sysobjects]
-- mbsII -the name of database
where xtype='U'
-- U - user table
OPEN table_names
FETCH NEXT FROM table_names INTO @table_name
IF @@FETCH_STATUS <> 0
PRINT 'No tables'
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_spaceused @table_name
FETCH NEXT FROM table_names INTO @table_name
END
CLOSE table_names
DEALLOCATE table_names
print '------------------------------------------------'
print GetDate()
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply