SQL Query to List all tables with their Row counts in a Database

Hi there...

I was in need to analyze a database. This SQL Query to List all tables with their Row counts in a Database was pretty much useful.

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

Hope it helpz
Fauzi

How to delete all records from all tables from Database

Dear Reader,

Following is the SQL query used to delete all records from all tables from Database.
This is useful during development scenarios where you need to get rid of all the dummy records and insert a fresh data.

SQL Query: Appropriate comments are provided to explain its operation

/* The Following set of queries is used to delete all records from all tables from your database */

/* Disables Referential integrity */

/*---------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

/* Delete records from tables */

/*----------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?

'

GO

/* Enables Referential integrity */

/*-------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

/*Query ot be used if you would like to reseed all table*/

/*------------------------------------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

Hope its Helpful

Regards
Fauzi