Solved

t-sql looping through a temp table

Posted on 2010-08-19
4
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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