Solved

t-sql looping through a temp table

Posted on 2010-08-19
4
370 Views
Last Modified: 2012-05-10
I have a temp table that contains database names that exist on on my SqlServer and would like to pass the varaible fo the databse name in order to fetch data in sysobjects and syscolumns for each database.

Below is a stored procedure code where ExtractorControl is a database that will always be in the code but 'gmc' is one of the databases that exists in the temp table. I would like to alter this working code so that I can loop through each database (ApplicationCode) and acumulate the rows that I need from each sysobjects and syscolumns within each database.
This is the temp table:



SELECT DISTINCT ApplicationCode FROM  ExtractorControl..EditChecks



This is where I want the ApplicationCode parameter to play:



    SELECT ExtractorControl..FilesToProcess.ApplicationCode, ExtractorControl..FilesToProcess.RootFile, ExtractorControl..FilesToProcess.DestinationTable, gmc..syscolumns.name AS 'Column Name'

    INTO #TableStructures

    FROM (ExtractorControl..FilesToProcess INNER JOIN gmc..sysobjects ON ExtractorControl..FilesToProcess.DestinationTable = gmc..sysobjects.name) INNER JOIN gmc..syscolumns ON gmc..sysobjects.id = gmc..syscolumns.id

    GROUP BY ExtractorControl..FilesToProcess.ApplicationCode, ExtractorControl..FilesToProcess.RootFile, ExtractorControl..FilesToProcess.DestinationTable, gmc..syscolumns.name, gmc..sysobjects.xtype

    HAVING (gmc..sysobjects.xtype)= 'U'

Open in new window

0
Comment
Question by:LennyGray
  • 3
4 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 33482491
You need to use dynamic sql.
checxk out this
DECLARE @DBName VARCHAR(50)

DECLARE @STRSQL VARCHAR(MAX)

SET @strSQL =''



declare curDBName CURSOR  FOR SELECT name FROM sysdatabases

OPEN curDBName

FETCH NEXT FROM curDBName INTO @DBName

WHILE @@FETCH_STATUS=0

BEGIN

	

	SET @strSQL =@strSQL + 'Select * from '+ @DBName +'.dbo.sysobjects where WHERE xtype=''FN''' + CHAR(10)

	



FETCH curDBName INTO @DBName

END

CLOSE curDBName

DEALLOCATE curDBName





PRINT @STRSQL

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33482494
To excute query

replace PRINT @STRSQL to EXEC @STRSQL.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33482512
I would advice you to use Table Variable Instead of #Table.
Because you looping the data so u might need to check condition that if table is exists or not.

FOR INFO ON table variable
 
http://odetocode.com/Articles/365.aspx

0
 
LVL 10

Author Closing Comment

by:LennyGray
ID: 33486061
Outstanding!!!

Thank you!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now