trying to loop through all database on server to get a count of tables and stored procedures on each database

Question

trying to loop through all database on server to get a count of tables and stored procedures on each database

gchappell

SSC Eights!

Points: 845
More actions
December 4, 2012 at 1:51 pm

#267781

trying the following cursor but the insert statement remains in the current db and does not switch. the cursor itself changes @dbName each time it loops through.
CREATE TABLE TestTable (table_count int, table_catalog nvarchar(25))
DECLARE @dbName sysname
DECLARE AllDBCursor CURSOR STATIC LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','tempdb','model','msdb') ORDER BY name
OPEN AllDBCursor; FETCH AllDBCursor INTO @dbName;
WHILE (@@FETCH_STATUS = 0) -- loop through all db-s
BEGIN
INSERT INTO TestTable (table_count, TABLE_CATALOG)
SELECT COUNT(*) AS TABLE_COUNT, TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
group by TABLE_CATALOG
FETCH AllDBCursor INTO @dbName
END -- while
CLOSE AllDBCursor; DEALLOCATE AllDBCursor;
Select * from TestTable
DROP TABLE TestTable

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's because you have have to specify the database name in front of information_schemas, otherwise it's the current context.

i personally avoid the information_schemas, and go for the SQL views instead;

this is something fairly equivalent to what you were asking:

--DROP TABLE [#RESULTS]

CREATE TABLE [dbo].[#RESULTS] (

[DBNAME] NVARCHAR(128) NULL,

[OBJECTCOUNT] INT NULL,

[TYPE_DESC] NVARCHAR(60) NULL)

EXEC sp_MSforeachdb

'INSERT INTO #RESULTS

select

''?'' As DbName,

COUNT(*) As ObjectCount,

type_desc

from [?].sys.objects

WHERE type_desc IN(''USER_TABLE'',

''VIEW'',

''SQL_STORED_PROCEDURE'',

''SQL_TABLE_VALUED_FUNCTION'',

''SQL_SCALAR_FUNCTION'',

''SQL_INLINE_TABLE_VALUED_FUNCTION'')

group by type_desc'

SELECT * FROM [#RESULTS] ORDER BY DBNAME,Type_desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gchappell SSC Eights! Points: 845 More actions · Answer 2

Thanks Lowell tha worked. I like the idea of the views

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This will give it to you in a single result set using dynamic SQL:

declare @Cmd nvarchar(max)

select

@CMD =

isnull(@cmd+N'union all',N'')+

N'

select

[DB]= N'''+db.name+N''' ,

[Type Count]= count(*),

[Type_Desc]= [Type_Desc]

from

'+quotename(db.name)+N'.sys.objects a

group by

[Type_Desc]

'

from

sys.databases db

where

db.source_database_id is null

order by

db.name

set @cmd = @cmd +

N'order by

[DB],

[Type_Desc]

'

exec (@cmd)

DB Type Count Type_Desc

----------------- ----------- -------------------------------------

MyProdDB 5 CHECK_CONSTRAINT

MyProdDB 289 DEFAULT_CONSTRAINT

MyProdDB 163 FOREIGN_KEY_CONSTRAINT

MyProdDB 85 INTERNAL_TABLE

MyProdDB 240 PRIMARY_KEY_CONSTRAINT

MyProdDB 3 SERVICE_QUEUE

MyProdDB 1 SQL_INLINE_TABLE_VALUED_FUNCTION

MyProdDB 35 SQL_SCALAR_FUNCTION

MyProdDB 914 SQL_STORED_PROCEDURE

MyProdDB 5 SQL_TABLE_VALUED_FUNCTION

MyProdDB 8 SQL_TRIGGER

MyProdDB 45 SYSTEM_TABLE

MyProdDB 49 UNIQUE_CONSTRAINT

MyProdDB 251 USER_TABLE

MyProdDB 166 VIEW

DBAUtility 1 DEFAULT_CONSTRAINT

DBAUtility 1 FOREIGN_KEY_CONSTRAINT

DBAUtility 5 INTERNAL_TABLE

DBAUtility 3 PRIMARY_KEY_CONSTRAINT

DBAUtility 3 SERVICE_QUEUE

DBAUtility 1 SQL_INLINE_TABLE_VALUED_FUNCTION

DBAUtility 5 SQL_STORED_PROCEDURE

DBAUtility 45 SYSTEM_TABLE

DBAUtility 7 UNIQUE_CONSTRAINT

DBAUtility 3 USER_TABLE

DBAUtility 2 VIEW

master 1 DEFAULT_CONSTRAINT

master 4 INTERNAL_TABLE

master 3 SERVICE_QUEUE

master 2 SQL_STORED_PROCEDURE

master 58 SYSTEM_TABLE

master 6 USER_TABLE

model 5 INTERNAL_TABLE

model 3 SERVICE_QUEUE

model 45 SYSTEM_TABLE

msdb 8 CHECK_CONSTRAINT

msdb 222 DEFAULT_CONSTRAINT

msdb 63 FOREIGN_KEY_CONSTRAINT

msdb 8 INTERNAL_TABLE

msdb 89 PRIMARY_KEY_CONSTRAINT

msdb 6 SERVICE_QUEUE

msdb 8 SQL_INLINE_TABLE_VALUED_FUNCTION

msdb 33 SQL_SCALAR_FUNCTION

msdb 433 SQL_STORED_PROCEDURE

msdb 1 SQL_TABLE_VALUED_FUNCTION

msdb 38 SQL_TRIGGER

msdb 10 SYNONYM

msdb 45 SYSTEM_TABLE

msdb 1 TYPE_TABLE

msdb 25 UNIQUE_CONSTRAINT

msdb 141 USER_TABLE

msdb 80 VIEW

tempdb 9 INTERNAL_TABLE

tempdb 3 SERVICE_QUEUE

tempdb 45 SYSTEM_TABLE

tempdb 8 USER_TABLE