You can remove numbering with the '-n' OSQL parameter.

However, you might consider using dynamic SQL to accomplish the task.
Example below:

SET NOCOUNT ON
CREATE TABLE #TableRowCounts
(
TableName nvarchar(261) NOT NULL,
TableRowCount bigint NOT NULL
)
DECLARE
@TableName nvarchar(261),
@SqlStatement nvarchar(500)
DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME) AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO @TableName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
N'INSERT INTO #TableRowCounts
SELECT ''' + @TableName + N''', COUNT(*)
FROM ' + @TableName + N' WITH (NOLOCK)'
EXEC (@SqlStatement)
END
CLOSE TableList
DEALLOCATE TableList

SELECT *
FROM #TableRowCounts
WHERE TableRowCount > 0
ORDER BY TableName


--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Bob" <rms@robertsiegel.net> wrote in message
news:91f443f8.0311141523.10db27e5@posting.google.c om...
> Everybody,
>
> I've been doing a lot of on-line research and cannot find
> any reference to the exact problem I'm having.
>
> Let me preface this question with the fact that I'm coming
> from an Oracle background so my approach may not be the best
> way to tackle this. However, from the research I have done
> this approach seems reasonable. Also, I know about the
> undocumented procedure sp_MSforeachtable. That can give me a
> result similar to what I'm looking for but the format of the
> output is not what I need.
>
> Now the problem. I'm trying to write a reusable script to give
> me a list of all the tables in a database that have 1 or more rows.
> My approach is to a BAT file (see script 1 below) that calls OSQL
> twice, once to call a SQL script (see script 2 below) that uses the
> Information_Schema views to generate the SELECT COUNT(*) statements
> and fill in all the tables names in the database, write this to a
> temporary output file and the second OSQL command to read the
> temporary output file and generate me the results formatted the
> way I need.
>
> The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>
> 6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.
> Because of this garbage the 2nd OSQL command blows up! Anyone have
> any idea what is generating this garbage?
>
> If I manually edit out the garbage and then just run the 2nd OSQL
> command
> I get similar garbage in the final result file (see 2nd result file
> below).
>
> In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manually
> then take its output and copy and paste it to a new query window and
> run that it works OK except for generating lots of blank lines where
> the result of the tables that have zero rows are. I am suppressing
> headings but am still getting the blank lines but at least it works!
>
> Any ideas anybody? Thanks For Any Help
> FYI -- SQL Server 2000 with SP3a.
> Bob
>
> ================== Script 1 - BAT File to Call OSQL ===============
>
> @echo off
> @echo ************************************************** *************
> @echo .
> @echo get_table_count.bat
> @echo .
> @echo Before you run this script change to the drive and directory
> @echo where the input SQL script is located!
> @echo .
> @echo Input parameters:
> @echo 1) SQL Server userid
> @echo .
> @echo You will be prompted twice for your password!
> @echo .
> @echo The output is written to file TABLE_COUNT_RESULT.TXT
> @echo .
> @echo ************************************************** *************
> pause
> osql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -o
> temp_table_count_query.txt -h-1 -w500
> osql -U %1 -S devkc-db -d C3T_Architecture -i
> temp_table_count_query.txt -o table_count_result.txt -h-1 -w500
> del temp_table_count_result.txt
> @echo on
>
> ================================================== ====================
>
> ================ Script 2 - GET_TABLE_COUNT.SQL Script ===============
>
> set nocount on
> select 'set nocount on'
> select 'select ''Table Name Count'''
> select 'select ''========== ====='''
> select 'select '''
> + table_name
> + ''', count(*) from '
> + table_name
> + ' having count(*) > 0 '
> from information_schema.tables
> where table_type = 'BASE TABLE'
> order by table_name
>
> ================================================== ====================
>
>
> ============ Partial Result of 1st OSQL Run ==========================
>
> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount on
>
> select 'Table Name Count'
>
> select '========== ====='
>
> select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE having
> count(*) > 0
> select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0
> select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0
>
> ================================================== ====================
>
>
> ============ Partial Result of @nd OSQL Run ==========================
>
> 1> 2> 3> 4> ... I edited out the intervening numbers for this message
> ... 664> 665> 666> 667> Table Name Count
>
> ========== =====
>
> ... I edited out lots of blank lines in the result for this message
> before I get to the first table with 1 or more rows ...
>
> ARCH 6
>
> ================================================== ====================