Cursor Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Cursor Problem

Author

Topic

a.shahnazi
Starting Member

3 Posts

Posted - 2009-07-29 : 02:05:43

I wrote a script for finding number of objects :

DECLARE db_name_cur CURSOR
FORWARD_ONLY
FOR SELECT Name
FROM master.dbo.sysdatabases

CREATE TABLE #temp (
DatabaseName NVARCHAR(128),
UserTable INT ,
SyetemTable INT,
PrimaryKey INT
)

DECLARE @db_name nvarchar(128)
DECLARE @SQL nvarchar(150)
DECLARE @UTab INT
DECLARE @STab INT
DECLARE @PK INT

OPEN db_name_cur

FETCH NEXT FROM db_name_cur INTO @db_name
WHILE @@fetch_status = 0
BEGIN

SET @SQL ='use ' + @db_name
exec (@SQL)
SET @UTab = (select count(*) from sysobjects where xtype = 'U' )
SET @STab = (select count(*) from sysobjects where xtype = 'S')
SET @PK = (select count(*) from sysobjects where xtype = 'PK')

INSERT #temp VALUES
( @db_name ,@UTab,@STab,@PK )
FETCH NEXT FROM db_name_cur INTO @db_name
END
SELECT * FROM #temp
CLOSE db_name_cur
DEALLOCATE db_name_cur
DROP TABLE #temp

This script dosn't work correct and just fetchs information of current database in Query Analyzer.
Does anyone have any idea ?

Regards
Ali

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 04:17:13

Hi

change to dynamic....like this
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'select count(*) from '+@db_name+'..sysobjects where xtype = ''U'' '
EXEC(@SQL)

-------------------------
R..

a.shahnazi
Starting Member

3 Posts

Posted - 2009-07-30 : 00:24:41

Thanks for your advice
this is the final :

SET @SQL = N'SELECT @UTab = COUNT(*) FROM ' + @db_name + '..sysobjects where xtype = ' + '''U'''
EXEC sp_executesql @query = @SQL, @params = N'@UTab INT OUTPUT' , @UTAB = @UTAB OUTPUT

Regards
Ali

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources