how to find no. of databases and tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to find no. of databases and tables?

Author

Topic

SanjaySutar
Starting Member

18 Posts

Posted - 2008-03-26 : 12:08:09

Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???

If any server variable is used then please mention that???

thanx in advance

San

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-26 : 12:16:59

To find out no. of databases, query master..sysdatabases table
To find out no. of tables in db, query information_schema.tables view.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-26 : 12:18:58

Tables - SELECT distinct count(*) FROM INFORMATION_SCHEMA.TABLES

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 12:33:59

You don't have to query anything. Just open management studio and expand database ,you will number of database and tables in the right hand side. But you have to take off system database and system tables from count you have got.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-26 : 12:42:10

Sodeep,

If you haven't noticed, OP is specifically asking for SQL query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 12:44:34

Agreed. Bcoz I am DBA, I am lazy at Query.

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 15:46:41

I know this is not the best as it has a cursor and dynamic SQL, but I have not been able to do it without. (Running on SQL2k5).

-- List all databases
SELECT name
FROM sys.databases

-- List all tables in each database, excludes system tables.
DECLARE @DBName sysname
CREATE TABLE #DBTable (DBName sysname, TableName sysname)

DECLARE DatabaseNameCursor CURSOR FOR
SELECT name
FROM sys.databases

OPEN DatabaseNameCursor
FETCH NEXT FROM DatabaseNameCursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT #DBTable SELECT ''' + @DBName + ''', name FROM ' + @DBName + '.sys.tables WHERE is_ms_shipped <> 1')

FETCH NEXT FROM DatabaseNameCursor INTO @DBName
END

CLOSE DatabaseNameCursor
DEALLOCATE DatabaseNameCursor

SELECT *
FROM #DBTable

DROP TABLE #DBTable

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 15:49:26

You don't need cursor for finding counts on databases and tables. That will be crazy.

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 15:56:13

Err, just looked at my solution vs the OP's request. My solution lists the tables, not the count. I still am not able to get the counts without executing the query against each database (hence, a cursor) as INFORMATION.SCHEMA only has tables for the database the query is run against. Or am I missing something.

Without a cursor, how would you produce one listing of database name and number of tables?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 15:58:23

Did you read carefully what Harsh and I have posted?

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 16:04:11

Yes, but that does not list for ALL databases.

Query: SELECT distinct count(*) FROM INFORMATION_SCHEMA.TABLES
Results: Number of tables in the database run against, not in ALL databases.

Run against master gives 16 on my system.
Run against northwind gives 30 on my system.

I am asking how you would provide one listing of all database and their table counts without using a cursor?

Example desired output:
Master 16
Northwind 30
JunkDB 9
etc.....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 16:05:31

You can use this also:

Databases:

select count(name) from sys.databases

Tables:

select count(name) from sysobjects
where type ='U'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 16:06:59

Ok now i got what you mean:

You can use Exec sp_MsforeachDB

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 16:10:28

Running against master:
select count(name) from sysobjects
where type ='U'

Results in: 16.

This is not the answer to the question I am asking: How to give a listing of all database, and table counts for each database without using a cursor.

Example output.
Master 16
Northwind 30

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 16:53:01

Thanks. Using sp_foreachdb sure is a whole lot cleaner than a cursor.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 16:55:44

Have you ever checked the functionality of SP_MsforeachDB? you need to read that in BOL before you try to run horrible cursor to find all tables in all database?

ocary
Starting Member

12 Posts

Posted - 2008-03-26 : 17:16:08

Searching through MSDN forums, sp_msforeachdb is unsupported and may go away in the future. If anyone knows any different, please post pertinent info.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820643&SiteID=1&pageid=0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-26 : 18:05:44

[code]
create table #mydbs (dbname nvarchar(255), numtables int)
declare @data nvarchar(2000), @dbname nvarchar(255)

while (
select count(name)
from master.dbo.sysdatabases
where dbid > 5
and [name] not in (select dbname from #mydbs)
) > 0
begin
select top 1 @dbname = name, @data = 'select ''' + Cast(Name as varchar) + ''' as dn, count(*) as nt from [' + Cast(Name as varchar) + '].dbo.sysobjects where xtype = ''U'';'
from master.dbo.sysdatabases
where dbid > 5
and [name] not in (select dbname from #mydbs)

insert into #mydbs
exec sp_executesql @data
end

select * from #mydbs order by dbname
drop table #mydbs

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-26 : 19:22:23

In SQL Server 2005


declare @sql nvarchar(max)

select @sql = isnull(@sql+' union all'+char(13)+char(10),'')+a.SQL
from
	(
	select
		aa.CATALOG_NAME,
		SQL =	'select [DATABASE]='''+
			aa.CATALOG_NAME+
			''', [Count]=count(*) from '+quotename(aa.CATALOG_NAME)+
			'.INFORMATION_SCHEMA.TABLES b where b.TABLE_TYPE = ''BASE TABLE'''
	from
		INFORMATION_SCHEMA.SCHEMATA aa
	) a
order by
	a.CATALOG_NAME

exec (@sql)


Results:
DATABASE  Count       
--------- ----------- 
master    10
model     0
msdb      76
Northwind 13
pubs      11
tempdb    0

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 19:25:34

Great job Michael.

ocary
Starting Member

12 Posts

Posted - 2008-03-27 : 12:18:08

Michael, running the script you posted on SQL 2005 returns:

DATABASE Count
-------- -----------
master 16
master 16
master 16
.... (repeats for 14 total rows).

I could be wrong, but I thought the INFORMATION_SCHEMA view was only for the current database, so I don't see how your script could yield results for all databases.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources