SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 05:26 PM
| ||||
| ||||
 Re: SQL Query To List Tables And Their Row Count Hi Mike, One solution is to use cursor with the list of table names and to execute dynamically SQL string with each cursor value. Please don't use select count(*) method if you have tables with billions of rows. Here is the script (tested in Northwind, which has table names with spaces! That's why I have added square barckets!): declare @table_name sysname, @sql_string nvarchar(1000) set nocount on declare all_tables cursor for select TABLE_NAME from Norhwind.information_schema.tables where table_type='BASE TABLE' open all_tables fetch next from all_tables into @table_name while @@fetch_status=0 begin set @sql_string = N'select count(*) "' + @table_name + '" from [' + @table_name + ']' execute sp_executesql @sql_string fetch next from all_tables into @table_name end close all_tables deallocate all_tables set nocount off Hope this helps. Sinisa Catic "Mike Bannon" <mikeb@dataformation.co.uk> wrote in message news:<bh8gs5$asu$1@sparta.btinternet.com>... > Hi > > I want to create a list of all tables in a database along with their row > count. I can get the table list with: > > SELECT name FROM sysobjects WHERE xtype='U' > > but is there any way I can use this to feed a SELECT COUNT query? > > Or is there a different way of achieving this? > > TIA > Mike Bannon      |
Linear Mode
