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
================================================== ====================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...epartmentID=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
> ================================================== ====================|||Dan,
Thanks for the answers. I completely missed the -n option in the BOL.
I also like your alternative. Someone I work with suggested using a
temporary table then just selecting what I want but your approach
seems even more sophicated. I'm out of the office today so haven't
had a chance to try either answer but will as soon as possible.
Thanks so much,
Bob
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<Rkftb.917$Rk5.180@.newsread1.news.atl.earthlink.net >...
> 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...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
