Re: SP Question: Can I pass an input parameter for table name?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Russell Mangel (russell_at_tymer.net)
Date: 05/24/04 

Date: Mon, 24 May 2004 04:56:07 -0700

I was ready to give up on this post.

Thanks for the post, I will try your code
Kinda scary, looks like modem noise!

Russell Mangel
Las Vegas, NV

"miron" <nospam_miron.berlin2@verizon.net> wrote in message
news:gTpgc.18875$G_.17603@nwrddc02.gnilink.net...
> this is very simple :)
>
> exec <database name>.dbo.sp_MSforeachtable 'select ''$'', count(*)
from
> $', '$'
>
> -- cheers
>
>
> "Russell Mangel" <russell@tymer.net> wrote in message
> news:%23KpI3rbHEHA.3356@TK2MSFTNGP11.phx.gbl...
> > So you are saying that I can not use an input parameter for a table
name?
> >
> > Thanks
> > Russ
> >
> > "mac" <martin_kotuc@yahoo.com> wrote in message
> > news:#4d2YWVHEHA.3992@TK2MSFTNGP10.phx.gbl...
> > > Here's a code sample which produces the number of rows for each table
in
> > > current database:
> > >
> > > ==============================================================
> > > declare @tableName varchar(50)
> > > declare @sql varchar (200)
> > >
> > > declare curTables cursor fast_forward
> > > for
> > > select TABLE_NAME from information_schema.tables
> > >
> > > open curTables
> > > fetch next from curTables into @tableName
> > >
> > > while (@@FETCH_STATUS = 0)
> > > begin
> > > set @sql = 'select '''+ @tableName +''' AS TableName, count(*) as
> RCount
> > > from ' + @tableName
> > > exec (@sql)
> > > fetch next from curTables into @tableName
> > > end
> > >
> > > close curTables
> > > deallocate curTables
> > >
> > > ==============================================================
> > >
> > > mac
> > >
> > >
> > > "Russell Mangel" <russell@tymer.net> wrote in message
> > > news:eftAGMVHEHA.1192@TK2MSFTNGP11.phx.gbl...
> > > >
> > > > I am trying to create a stored procedure in Query Analyzer.
> > > > I am trying to pass an input parameter, for a table name.
> > > > I need to get the rowcount for each of 97 tables in an SQL 7.0
> database.
> > > >
> > > > My attempts have failed, can you make my SP work correctly?
> > > >
> > > > CREATE PROCEDURE MiniCimDB_InsertRow
> > > > @tableName varchar(100),
> > > > @TotalRows int OUTPUT
> > > > AS
> > > >
> > > > DECLARE @tmp varchar(100)
> > > >
> > > > SET @tmp = @tableName
> > > > SELECT @TotalRows = COUNT(*)
> > > > FROM @tmp
> > > > GO
> > > >
> > > > DECLARE @ttotal int
> > > > Exec MiniCimDB_InsertRow 'mailItems', @ttotal OUTPUT
> > > > print @ttotal
> > > >
> > > > Thanks
> > > > Russell Mangel
> > > > Las Vegas, NV
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages