Dynamic Query

I am trying to write some code to go through all the tables in my database
and do a row count. What I have done so far is open a cursor to sysobject
and loop round the tables in it. Then I am building a query in a string
"SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created
string. The problem is getting the result back from the count, can anybody
tell me how I do this?
thanks
Gav--Try this
Use Northwind
Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
SET @.tablename = 'Employees'
SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
print @.Count
~Bala|||Select object_name(id), rows from sysindexes where indid<1
Madhivanan|||<balacr@.gmail.com> wrote in message
news:1111658074.290946.83940@.f14g2000cwb.googlegroups.com...
> --Try this
> Use Northwind
> Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
> SET @.tablename = 'Employees'
> SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
> exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
> print @.Count
> ~Bala
>
Ths was great until I move the code from my Dev box to one of the servers
I want to run this on. Seems the sp_executeSQL does not exist, any ideas
why?
Gav|||Which version of SQL server are you using?
I am not sure whether 'sp_ExecuteSQL' was on SQL 7
Bala|||They are all on the same version SQL 2000 SP3a.. Only difference is the box
I am now trying to run it on is a SAP database server, box I wrote it on was
not.
Gav
<balacr@.gmail.com> wrote in message
news:1111665107.148760.270850@.g14g2000cwa.googlegroups.com...
> Which version of SQL server are you using?
> I am not sure whether 'sp_ExecuteSQL' was on SQL 7
> Bala
>|||Can you tell me the exact error that you are getting?|||Could not find stored procedure 'sp_executeSQL'.
<balacr@.gmail.com> wrote in message
news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
> Can you tell me the exact error that you are getting?
>|||My guess is that the server is case sensitive. The name of the procedure is
sp_executesql, not
sp_executeSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
> Could not find stored procedure 'sp_executeSQL'.
> <balacr@.gmail.com> wrote in message
> news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
>|||Spot on. Thanks to everyone for their help. :o)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwvlAeHMFHA.576@.TK2MSFTNGP15.phx.gbl...
> My guess is that the server is case sensitive. The name of the procedure
is sp_executesql, not
> sp_executeSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
>