?
Solved

t-sql looping through a temp table

Posted on 2010-08-19
4
Medium Priority
?
380 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

719 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