Solved

t-sql looping through a temp table

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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