Table counts - SQL Server Forums

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

Table counts

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-03-06 : 13:55:08

Hi,

I have 2 databases on the same server. DBA and DBB are suppose to be identical( have same triggers, function, procedures, tables, etc.....) Is there a way for me to do a table count comparison of these 2 databases and then have it tell me which tables have different counts?

Thanks

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 14:09:17

If you want to do full Schema Compare with code an etc; then you'll need to use like RedGate Compare, or ApexSQL Compare.

For simplying getting table count listing:

SQL 2005:

SELECT COUNT(*)
FROM sys.tables

SQL 2000:

SELECT COUNT(*)
FROM sys.objects
WHERE xtype = 'U'

To get column information:

SQL 2005:

SELECT T.name,count(*) AS ColumnCount
FROM sys.tables t, sys.columns c
WHERE t.object_id = c.object_id
GROUP BY t.name

SQL 2000:

SELECT T.name,count(*) AS ColumnCount
FROM sys.sysobjects o, sys.columns c
WHERE o.object_id = c.object_id
AND o.xtype = 'U'
GROUP BY t.name

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources