Solved

t-sql looping through a temp table

Posted on 2010-08-19
4
371 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Variable Event ? 3 27
Assigning handler to UserControls in flowlayoutpanel 4 22
Creating a route in asp.net webforms 2 25
SqlServer no dupes 25 34
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

815 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

8 Experts available now in Live!

Get 1:1 Help Now