November 14, 2003 at 4:10 pm
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 Siegel
================== 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
======================================================================
November 17, 2003 at 6:54 am
quote:
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.
What you see is not garbage.
The numbers indicate the order in which the sql commands would have executed at the command prompt.
Try specifying "GO" after each sql statement in ur script file.
Sachin
Regards,
Sachin Dedhia
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply