Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

t-sql looping through a temp table

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
LennyGray
Asked:
LennyGray
  • 3
1 Solution
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
To excute query

replace PRINT @STRSQL to EXEC @STRSQL.
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
LennyGrayAuthor Commented:
Outstanding!!!

Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now