all groups > sql server programming > july 2006 >
You're in the

sql server programming

group:

Cursor Question



Cursor Question Chris
7/6/2006 9:12:02 PM
sql server programming: I have a cursor that pulls the name of the tables one by one on my server and
I need to use it in a sql statement to compare the counts to the same table
names in another server. How can I get the Cursor that holds the name of the
table to work in the from clause? It keeps giving me an error.

See below

USE reportingcurrent
GO

Declare @Table varchar(100)
Declare @RepCur varchar(50)
Declare @Stag varchar(50)

Set @repCur = 'reportingcurrent.dbo.'
Set @Stag = 'staging.dbo.'

DECLARE TableName1 CURSOR FOR
SELECT [name]
FROM sysobjects
where reportingcurrent.dbo.sysobjects.xtype = 'u'
order by [name]

OPEN TableName1
--GO

FETCH NEXT FROM TableName1
into @Table
--GO

WHILE @@FETCH_STATUS = 0
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
If (Select Count(1) from reportingcurrent.dbo.TableName1) >
(Select Count(1) from staging.dbo.TableName1)
Begin
Truncate Table DateTimeStamp
insert into reportingcurrent.dbo.datetimestamp
values (@Table, GetDate()-1)
End
Else
Print 'False'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM TableName1
Into @Table
END

CLOSE TableName1
DEALLOCATE TableName1
GO

here is my error message

Server: Msg 208, Level 16, State 1, Line 25
Invalid object name 'reportingcurrent.dbo.TableName1'.
Server: Msg 208, Level 16, State 1, Line 25
Invalid object name 'staging.dbo.TableName1'.


Any and all help is appreciated.

Thanks

-Chris
RE: Cursor Question Omnibuzz
7/6/2006 9:52:01 PM
you can check this link for getting a count of all tables.
http://www.aspfaq.com/show.asp?id=2428

But again, since it gives an approximation or uses undocumented procs, here
is another way

use pubs

create table #tblInfo
(id int identity(1,1), tbl_name varchar(50), rowcnt bigint)

declare @Table varchar(50)
DECLARE TableName1 CURSOR FOR
SELECT [name]
FROM sysobjects
where sysobjects.xtype = 'u'
order by [name]

OPEN TableName1
--GO

FETCH NEXT FROM TableName1
into @Table
--GO

WHILE @@FETCH_STATUS =
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
insert into #tblInfo (tbl_name,rowcnt) exec('select ''' + @table + ''' ,
(select count(*) from ' + @table + ')'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM TableName1
Into @Table
END


select * from #tblinfo

I have done it for pubs, it will give you the list of all tables and the row
count. you can use the same for the two databases and then join the temp
tables to get the result.

Hope this helps.
--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/
Re: Cursor Question Arnie Rowland
7/6/2006 10:27:35 PM
Somethig like this may work for you and be far more efficient than using =
a cursor. In place of the SELECT at the bottom, you can do whatever work =
you need with the data in the Temp tables.

SET NOCOUNT ON

CREATE TABLE #Tables
( TableName varchar(50)
, RowTotal int
)
CREATE TABLE #RemoteTables
( TableName varchar(50)
, RowTotal int
)

INSERT INTO #Tables
( TableName
, RowTotal
)
EXECUTE Northwind.dbo.sp_msForeachTable 'SELECT ''?'', COUNT(*) FROM =
?'
GO

INSERT INTO #RemoteTables
( TableName
, RowTotal
)
EXECUTE [sbs-westwood].northwind.dbo.sp_msForeachTable 'SELECT ''?'', =
COUNT(*) FROM ?'
GO

SELECT=20
t.TableName
, t.RowTotal AS 'Local RowCount'
, r.RowTotal AS 'Remote RowCount'
FROM #Tables t
LEFT JOIN #RemoteTables r
ON t.TableName =3D r.TableName

DROP TABLE #Tables
DROP TABLE #RemoteTables



--=20
Arnie Rowland*=20
"To be successful, your heart must accompany your knowledge."



[quoted text, click to view]
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---------------
[quoted text, click to view]
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---------------
[quoted text, click to view]
Re: Cursor Question Omnibuzz
7/6/2006 11:03:02 PM
The only problem is that its undocumented :)
--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/
Re: Cursor Question Arnie Rowland
7/6/2006 11:16:09 PM
I'd never recommend the various 'undoc' thingies for production application
use -but for administrative use...

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."



[quoted text, click to view]

Re: Cursor Question oj
7/6/2006 11:28:03 PM
sp_msforeach* use a cursor internally. So, you might as well create your =
own. This way you know exactly what the code is and it's 100% =
documented/supported.

--=20
-oj



[quoted text, click to view]
Somethig like this may work for you and be far more efficient than =
using a cursor. In place of the SELECT at the bottom, you can do =
whatever work you need with the data in the Temp tables.

SET NOCOUNT ON

CREATE TABLE #Tables
( TableName varchar(50)
, RowTotal int
)
CREATE TABLE #RemoteTables
( TableName varchar(50)
, RowTotal int
)

INSERT INTO #Tables
( TableName
, RowTotal
)
EXECUTE Northwind.dbo.sp_msForeachTable 'SELECT ''?'', COUNT(*) =
FROM ?'
GO

INSERT INTO #RemoteTables
( TableName
, RowTotal
)
EXECUTE [sbs-westwood].northwind.dbo.sp_msForeachTable 'SELECT =
''?'', COUNT(*) FROM ?'
GO

SELECT=20
t.TableName
, t.RowTotal AS 'Local RowCount'
, r.RowTotal AS 'Remote RowCount'
FROM #Tables t
LEFT JOIN #RemoteTables r
ON t.TableName =3D r.TableName

DROP TABLE #Tables
DROP TABLE #RemoteTables



--=20
Arnie Rowland*=20
"To be successful, your heart must accompany your knowledge."


=20
[quoted text, click to view]
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---------------
[quoted text, click to view]
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---------------
[quoted text, click to view]
Re: Cursor Question Mike C#
7/7/2006 1:02:06 AM
Here's a "creative" example that uses dynamic SQL and temp tables. I'm sure
it can be improved a lot, but I'm kinda tired right now... Enjoy...

DECLARE @server1 VARCHAR(255)
DECLARE @server2 VARCHAR(255)
DECLARE @database VARCHAR(255)

SELECT @server1 = 'myserver1'
SELECT @server2 = 'myserver2'

SELECT @database = 'mydatabase'

CREATE TABLE #tables_counts1 (num INT NOT NULL IDENTITY(1,1),
server VARCHAR(255) NOT NULL,
[name] VARCHAR(255) NOT NULL,
counts BIGINT,
PRIMARY KEY (server, [name]))

CREATE TABLE #tables_counts2 (num INT NOT NULL IDENTITY(1,1),
server VARCHAR(255) NOT NULL,
[name] VARCHAR(255) NOT NULL,
counts BIGINT,
PRIMARY KEY (server, [name]))

DECLARE @sql VARCHAR(4000)

SELECT @sql = 'INSERT INTO #tables_counts1 (server, [name]) ' +
'SELECT ''' + QUOTENAME(@server1) + ''', [name] ' +
'FROM ' + QUOTENAME(@server1) + '.' + QUOTENAME(@database) +
'.dbo.sysobjects ' +
'WHERE xtype = ''u'''

EXEC (@sql)

DECLARE @i INT
SELECT @i = 1
WHILE @i <= (SELECT MAX(num) FROM #tables_counts1)
BEGIN
SELECT @sql = 'UPDATE #tables_counts1 ' +
'SET counts = ( ' +
'SELECT COUNT(*) FROM ' + QUOTENAME(@server1) + '.' +
QUOTENAME(@database) + '.dbo.' + [name] + ') ' +
'WHERE num = ' + CAST(@i AS VARCHAR(32))
FROM #tables_counts1
WHERE num = @i
EXEC (@sql)
PRINT @sql
PRINT @i
SELECT @i = @i + 1
END

SELECT @sql = 'INSERT INTO #tables_counts2 (server, [name]) ' +
'SELECT ''' + QUOTENAME(@server2) + ''', [name] ' +
'FROM ' + QUOTENAME(@server2) + '.' + QUOTENAME(@database) +
'.dbo.sysobjects ' +
'WHERE xtype = ''u'''

EXEC (@sql)

SELECT @i = 1
WHILE @i <= (SELECT MAX(num) FROM #tables_counts2)
BEGIN
SELECT @sql = 'UPDATE #tables_counts2 ' +
'SET counts = ( ' +
'SELECT COUNT(*) FROM ' + QUOTENAME(@server2) + '.' +
QUOTENAME(@database) + '.dbo.' + [name] + ') ' +
'WHERE num = ' + CAST(@i AS VARCHAR(32))
FROM #tables_counts2
WHERE num = @i
EXEC (@sql)
PRINT @sql
PRINT @i
SELECT @i = @i + 1
END

SELECT *
FROM #tables_counts1 t1
FULL OUTER JOIN
#tables_counts2 t2
ON t1.[name] = t2.[name]

DROP TABLE #tables_counts2

DROP TABLE #tables_counts1

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages in the sql server programming group.
AddThis Social Bookmark Button
View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
home · blog · groups Questions? Comments? Contact the dn