Want to get deatils of tables and rows in multiple databases

Question

I'm looking for a bit of code to return database name, table name and row count across multiple databases in one instance of SQL Server 2000.

I have found lots of ways to get the information one database at a time but I would like to do all of them at once. I ultimately need to get them into an Excel Spreadsheet. If I do them one database at a time I then have to go through the pain of merging Excel worksheets.

Answer 1

Try

EXEC sp_MSForeachdb 'use [?]; select db_name();SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Always go step by step. Uri's code will give you the table name, databse name. Include something like this, which will give you the row count of that table.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

---

ANK HIT

Answer 3

Uri

That gets me database name, but shows details of only first 8 tables in a database and no row count information.

Answer 4

Ank

It's SQL Server 2000. It does not know Schema_Name

Answer 5

Ank

It's SQL Server 2000. It does not know Schema_Name

OH my bad! so its sql 2000, try this one. Since i don't have 2000 on my system so I am not sure but you try and let me know.

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

---

ANK HIT

Answer 6

Ank

It's SQL Server 2000. It does not know Schema_Name

Even you can create a temp table which stores such records and it uses the cursor. Since you want it for all the database, you can use the below script for all the database, simply by changing the database name.

USE [database name] ---- change the database name accordingly.
GO

DECLARE @name VARCHAR(500)
DECLARE @sql VARCHAR(1000)

CREATE TABLE #TableRecordCount
( TableName VARCHAR(100),
  RecordCount INT
)
 
DECLARE dbCursor CURSOR FAST_FORWARD
FOR
    SELECT T.NAME
    FROM SYSOBJECTS T
    WHERE T.XTYPE = 'U'
    ORDER BY T.NAME

OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @sql = 'INSERT INTO #TableRecordCount (TableName, RecordCount) '
      SET @sql = @sql + 'SELECT ''' + @Name + ''' AS TableName, COUNT(*) AS CNT '
      SET @sql = @sql + 'FROM [' + @name + ']'
      EXEC (@sql)
      FETCH NEXT FROM dbCursor INTO @name
END

CLOSE dbCursor
DEALLOCATE dbCursor

SELECT * FROM #TableRecordCount

DROP TABLE #TableRecordCount

---

ANK HIT

Answer 7

EXEC sp_msforeachtable 'select [?]=count(*) from ?'

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 8

This gives me the same result without the need for a temporary table

SELECT

    sysobjects.Name

    , sysindexes.Rows

FROM

    sysobjects

    INNER JOIN sysindexes

    ON sysobjects.id = sysindexes.id

WHERE

    type = 'U'

    AND sysindexes.IndId < 2

 ORDER BY

    sysindexes.Rows

 

But it's not what I am looking for. Ultimately I am looking to create something like this in Execel.

DBName	Table_Name	Number of Row
ADC	[dbo].[Sec2Sec]	91139527
ADC	[dbo].[Sectors]	10094
ADC	[dbo].[MFL_FERRY]	22
AuditInfo	[dbo].[kill_history_lockinfo]	269512
AuditInfo	[dbo].[Events]	101019
AuditInfo	[dbo].[dbsizer]	27265
AuditInfo	[dbo].[AuditObjecthistory]	26121
AuditInfo	[dbo].[activerowcount]	26093
AuditInfo	[dbo].[cm_Weeklyrowcount]	23961
AuditInfo	[dbo].[blkt_BlockLogs]	21165
AuditInfo	[dbo].[AlertHistory]	15194
AuditInfo	[dbo].[mflDatabaseSizes]	7129
AuditInfo	[dbo].[SuspensionFlagFix]	6038
AuditInfo	[dbo].[TRC_ALL_Z61PR9_16042002]	4440

I'm pretty sure it should be possible in one script. I'm just not getting it to work myself.

Answer 9

EXEC sp_MSForeachdb 'use [?]; select db_name();SELECT * FROM ?.INFORMATION_SCHEMA.TABLES;
SELECT
    sysobjects.Name,
    sysindexes.Rows

FROM

    sysobjects

    INNER JOIN sysindexes

    ON sysobjects.id = sysindexes.id

WHERE

    type = ''U''

    AND sysindexes.IndId < 2'

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 10

Check this blog post 

How to get information about all databases without a loop

I am sure it has a necessary script or a script you can adapt for your needs.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Naomi

Some great stuff in there. But they don't work with SQL 2000

Does exactly what I want on 2005 though

Answer 12

You may want to just use the idea and change for SQL 2000 to use sys.tables instead of INFORMATION_SCHEMA.Tables.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog