Please help
I am trying to return the number of records in tables in
our database using the script below. The problem is, I can
not format the EXEC() to return the table names and some
text. such as 'The number of rows in TABLENAME X '=' Y

DECLARE tables_cursor CURSOR
FOR
SELECT tbname FROM maxsyscolumns
OPEN tables_cursor
DECLARE @tablename varchar(30)
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
EXEC ('Select count(*) from '+ @tablename)

FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor

What about

CREATE TABLE CountRecsInTable(TableName varchar(50), CountOfRows int)
GO
EXEC sp_MSforeachtable "INSERT CountRecsInTable SELECT '?',COUNT(*) FROM ?"
GO



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"fumi" <fumi.omotayo (AT) ecovertfm (DOT) com> wrote

PLEASE HELP
Can someone please help. I need to modify the EXEC
statement below to display the table name aswell

Can you not just concatenate the @tablename variable into your exac
statement

something like

DECLARE @tname varchar(128)
set @tname = 'authors'
EXEC('select ''' + @tname + ''', count(*) from ' + @tname)



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"fumi.omotayo (AT) ecovertfm (DOT) com" <anonymous (AT) discussions (DOT) microsoft.com> wrote in
message news:538b01c3d9b5$5a5f0330$7d02280a (AT) phx (DOT) gbl...

Thanks Allan. Your reply answered my question.
Thanks again
fumi