InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:26:55 GMT20http://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxThe following code worked as bad as a [c u r s o r] is supposed to but at least it gave the expected result (had to change it to AdventureWorksDW though, since I'm using SS2K5). Side note: I consider the non-set-based solution as a "valid option" for this task, but definitely not in general (I can't even type the evil word...). :-)[code="sql"]FETCH NEXTFROM curtableINTO @TablenameWHILE @@FETCH_STATUS=0BEGINSELECT @xSET @x='select count(*)'+','+' '+@Tablename+' '+'from AdventureWorksDW2008.dbo.'+@TablenamePRINT (@x)FETCH NEXTFROM curtableINTO @TablenameEND[/code]Tue, 16 Mar 2010 16:09:26 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxThis works should show you how to do itBryandeclare @Tablename varchar(400)declare @x varchar(200)declare curtable cursorforselect namefrom sysobjects where type='U'order by nameopen curtablefetch nextfrom curtableinto @xset @x='select count(*) from '+@Tablenameprint @xexec (@x)while @@FETCH_STATUS=0begin set @x='select count(*) from '+@Tablename print @x exec (@x)fetch nextfrom curtableinto @Tablenameendclose curtabledeallocate curtableTue, 16 Mar 2010 16:07:58 GMTBryan Oliverhttp://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxcheers thanks heaps for this, used print and it does nothing. Is the set @x statement actually correct?Tue, 16 Mar 2010 15:52:01 GMTcraig 84462http://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxThanks, but have changed it to @Tablename in first fetch statement but tried "print" and getting nothing, it seems its correct though, anything else I've missed?Tue, 16 Mar 2010 15:51:04 GMTcraig 84462http://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxYou need to assign to @Tablename instead of @x in your first fetch statement.Also, just use "set @x ='select..."instead of "set @x =@x+'select..."Tue, 16 Mar 2010 15:43:18 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxThe easiest thing to do here is to display your query (using select or print) to verify it is what you expect before executing it as a query.Tue, 16 Mar 2010 15:40:57 GMTSteve Cullenhttp://www.sqlservercentral.com/Forums/Topic884265-391-1.aspxHi there,I was wondering if someone could tell me. I am learning dynamic sql and finding the '''s hard going and have decided to proceed with splitting them up. The following program message says its fine but I get no results window. Could someone tell me why?cheers Craig***************code*********************Use AdventureWorksDW2008godeclare @Tablename varchar(400)declare @x varchar(200)declare curtable cursorforselect namefrom sysobjects where type='U'order by nameopen curtablefetch nextfrom curtableinto @xwhile @@FETCH_STATUS=0beginset @x=@x+'select count(*)'+','+' '+@Tablename+' '+'from AdventureWorksDW2008.dbo.'+@Tablenameexec (@x) fetch nextfrom curtableinto @Tablenameendclose curtabledeallocate curtableTue, 16 Mar 2010 15:32:51 GMTcraig 84462