Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursor - passing @dbname to INSERT STATEMENT Expand / Collapse
Author
Message
Posted Thursday, January 8, 2009 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 5:41 PM
Points: 5, Visits: 183
I have a cursor that grabs all the database names and then needs to select data from the dbname.dbo.sysfiles table to insert into a DBGrowth table. The issue is that some of the Sharepoint database name have the (-) char so I need to concatenate '[' with the @dbname value from cursor to create the name of the table that will be used in thr FROM clause.

Here's the cursor code thus far. Any assistance woul be most appreciated.

DECLARE getDBName CURSOR FOR
select name from sys.databases

OPEN getDBName

DECLARE @dbname varchar(101)
DECLARE @dbsys varchar(101)

FETCH NEXT FROM getDBName INTO @dbname
SET @dbsys = '[' + @dbname + '.dbo.sysfiles]'

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT TEMPDB.dbo.DBGROWTH
SELECT @@servername, @dbname, getDate()
FROM @dbsys
FETCH NEXT FROM getDBName INTO @dbname

END

CLOSE getDBName
DEALLOCATE getDBName
Post #632746
Posted Thursday, January 8, 2009 1:09 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
Even though I do not like the cursor, I don't think there could be another way since you are trying to do some dynamic SQL There. Here's a solution, I've tried it, you can add the fields you want from the sysfiles table in the table, and it will refresh everytime.

USE tempdb

GO

DECLARE getDBName CURSOR FOR
SELECT name
FROM sys.databases

OPEN getDBName

DECLARE @dbname VARCHAR(101)
DECLARE @dbsys VARCHAR(101)
DECLARE @SQL NVARCHAR(2000)

IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[DBGrowth]')
AND TYPE IN (N'U'))
DROP TABLE [dbo].[DBGrowth]

CREATE TABLE DBGrowth (
ServerName NVARCHAR(128),
DbName NVARCHAR(128),
[size] NVARCHAR(128),
[MaxSize] NVARCHAR(128),
[growth] NVARCHAR(128),
DATE DATETIME)

FETCH NEXT FROM getDBName
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbsys = '['
+ @dbname
+ '].dbo.sysfiles'
SET @SQL = ('INSERT TEMPDB.dbo.DBGROWTH '
+ ' SELECT @@SERVERNAME, '''
+ @dbname
+ ''',size, maxsize, growth, getDate() '
+ ' FROM '
+ @DbSys)
PRINT @SQL

EXEC sp_executeSQL
@SQL
FETCH NEXT FROM getDBName
INTO @dbname

END

CLOSE getDBName
DEALLOCATE getDBName

SELECT *
FROM DBGrowth


Hope it helps,

Cheers,

J-F


Cheers,

J-F
Post #632786
Posted Thursday, January 8, 2009 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 5:41 PM
Points: 5, Visits: 183
Thank-you JF. Using dynamic SQL did the trick. Happy New Year.
Post #632847
Posted Thursday, January 8, 2009 6:36 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Another way, no cursor but still not amazing.

declare @sql varchar(MAX)
set @sql = ''

create TABLE #temp (ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, ServerName sysname, DBName sysname, Size int, ReadingDate datetime)

select @sql = @sql + 'INSERT INTO #temp (ServerName, DBName, Size, ReadingDate) select @@ServerName, ''' + name + ''', Size, GETDATE() from [' + name + '].[dbo].[sysfiles];'
from master.sys.databases

EXEC (@sql)

SELECT * FROM #temp



Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #633010
Posted Friday, January 9, 2009 7:22 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
Nice, I have to say I prefer this one a thousand times, since there is no cursor. I did not know you could append in a string from a multirow query. Great job!

Cheers,

J-F


Cheers,

J-F
Post #633372
Posted Friday, January 9, 2009 10:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
Yet another way, using Matt's example to build on.

create TABLE #temp (ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, ServerName sysname, DBName sysname, Size int, ReadingDate datetime)

EXEC sp_MSforeachdb ' USE ?
INSERT INTO #temp (ServerName, DBName, Size, ReadingDate)
SELECT @@ServerName as ServerName,
Name,
Size,
GETDATE() as ReadingDate
FROM sys.database_files'

SELECT * FROM #temp
drop table #temp





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #633625
Posted Friday, January 9, 2009 10:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
Isn't this feature deprecated? I thought I read not to use this somewhere, maybe my memory had gone wacko, but I prefer to do this in a select, then using a deprecated feature.

Cheers,

J-F


Cheers,

J-F
Post #633629
Posted Friday, January 9, 2009 10:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
I am not aware of it being depricated. It exists in SQL Server 2008 as well.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #633635
Posted Friday, January 9, 2009 1:38 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
John Rowan (1/9/2009)
I am not aware of it being depricated. It exists in SQL Server 2008 as well.

It's not deprecated, but it is a cursor under the hood. AFAIK.

You can script sp_ stored procedures if you use SMO, or another IDE.


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #633837
Posted Friday, January 9, 2009 1:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
Yep. Many of the interworkings of SQL Server are cursors under the hood. This just beats writing one and works fine for admin tasks.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #633846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse