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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  December 4, 2012 at 2:25 pm

  #1565503

  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

  December 4, 2012 at 2:37 pm

  #1565507

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

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  December 4, 2012 at 3:01 pm

  #1565512

  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